Calculate Pro-Rata Share of Two Variables

My [lack of] math skills are failing me so thanks in advance for your time. Here is the problem, and the attachment provides a visual and formula template:

Bob invests $75,000 in CompanyX on January 1st 2011.
Tom invests $25,000 in CompanyX on July 1st 2011.

At the end of the year, CompanyX disburses $20,000 in net profit to investors. I am trying to write a formula that determines how much money Bob and Tom would get, respective to the amount they invested as well as when they invested it.

For example, without the time consideration, Bob should get 75% of 20k or $15,000, and Tom should get 25% of 20k or $5,000. However, in reality that is not right...because then Tom would be getting a much higher annual return that Bob gets since he only had his money tied up in CompanyX for half the year. I'd really like the formula to be able to do this for at least three investors (Bob, Tom, and Frank).

Assuming disbursements are only made once per year after the year has ended, my end goal is to use the below three variables to determine the "pro-rata" share of net profit each investor should get (with at least three investors). The variables are:
1) Amount Invested 2) Date of Investment 3) Total Net Profit in Dollars at Year's End

Thank you for any ideas on how to tackle this.

-Chris

I am an engineer, not a finance guy, but here's what I would do (see attached Step 3).

The weight of an investment depends on the amount invested (A) and the duration of time (T) it's invested for. So let's establish that weight as the product of the two, A x T. This is done in a formula with a multiplication.

Then I find the sum of all weights. This is done with the SUMPRODUCT function.

I divide the individual investor's weight by the sum of all weights to get the proportion of his share.

Then I multiply the whole thing times the profit to be disbursed. In your example, weights are

Bob 75,000 x 365 = 27,375,000
Tom 25,000 x 184 = 4,600,000
Total = 31,975,000

As you can see Bob's greater investment combined with longer duration gives him the lion's share of the profit.

Originally Posted by wealthistime

Thanks a million Bob- that works!

Bob's the guy in your spreadsheet. My name is Jeff