Percentage of Ownership Formula

[Bill Booth]

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.

 

[Mike H]

There are several ways and this is probably the simplest

Put the purchase price in a1
Put this formula in b1
=IF(C1="","",A1/C1)

enter different numbers in c1 to get the amount depending on partners.

Mike

 

[Glenn]

I am buying a new business with partners. The cost is $386,450. We may have
up to 10 partners, so each would contribute $38,645. I would like to build a
table that allows "what if" scenario. For example if we only get 8 partners
how much do the 8 need to contribute, or 6, 9, 7 and so on. How can I develop
a formula that will allow me add to remove partner contribution levels and
still keeping the total amount $386,450? I would like to do this real time
during a presentation with the partners present.

The cost per share of the new business is $386,450/n with n being the number of
shares. Put $386,450 in cell A1 and the following in B1 and C1, copying down as
far as necessary:

B1=ROW()
C1=$A$1/ROW()

 

[John C]

If you want to get a little fancier, say, for example, some people didn't
want a 'full share' only 50% share, etc. You could do the following:
A1: Purchase Price
B1*: Shares
C1: =IF(OR(A1="",B1=""),"",A1/B1)

Shares is equal to the number of partners you have, however, say you have 6
people that want to be full partnes, and 2 people that wanted to be less than
a full partner, say one is 75%, and one is 50%. Then the subsequent shares
would be equal to 6*1+.75*1+.5*1 = 7.25
A Share price is equal to: $386,450/7.25 = $53,303.45
All full partners would pay the $53,303.45
The 75% partner would pay 75% of 53,303.45 = $39,977.59
The 50% partner would pay 50% of 53,303.45 = $26,651.72

 

[Bill Booth]

John C,
Thank you vey much for your help and such a quick response. Your thought was
right on. The Partners will not be equal share holders. I have tried your
formula and am having a problem. Could I ask for a little more help?
Following are the actual percentages that the potential partners want to
contribute.
Purhcase price is $386,450. there are 9 potential partners their
contribution amounts are as follows.
20%
9%
18%
3%
3%
26%
11%
3%
8%

I can not seem to make your formula work
Thanks again
Bill

 

[Bill Booth]

Oh yea, one other thing just to clarify. If one of the partners drops out the
others will need to add to their contribution proportionately to make up the
difference. The total price needs to be $386,450.

 

[Meebers]

by simple math here, adding the %'s = 101%....unless there is some rounding
going on??

 

[Bill Booth]

yes its a rounding issue. The total should be 100%

 

[John C]

Firstly, your percentages as given add up to 101%, not 100%, but that is
neither here nor there.
This is what I recommend:
Cells A1:A9, type in your given given percentages. In my example, I have
assumed partner 9 is actually 7%, instead of 8%. However, do not type these
in as percentages, type them in as whole numbers, like so:
20,9,18,3,3,26,11,3,7, this totals, effectively, 100 shares.
A10: =SUM(A1:A9) ... 100
B10: =386450.00 ... (total price)
B11: =SUM(B1:B9) ... (total of all individual amounts, should be equal to B10)
D1: =$B$10/$A$10 ... (price per share = $3,864.50)
B1: =A1*$D$1 ... (#shares * price per share) ... copy down through B9
C1: =B1/$B$10 ... format cell as percentage ... copy down through C9
C10: =SUM(C1:C9) ... should be 100%

Now, if someone decides to not join in, all you need to do is highlight the
row that that person was, columns A through C, and press the DELETE key.
Your value in A10 will decrease by the appropriate 'shares' number, B11 will
remain at the full amount, C10 will remain at 100%. Your per share cost in D1
will go up. And your percentages, that looked nice and neat originally in
cells C1:C9, will change. However, if you calculate it out, any remaining
amounts will still be in proportion to each other, as they were originally.
For example #2 @ 9% and #3 @ 18%, assuming they are one of the ones that
left, #3 will still be paying twice as much as #2.

Hope this helps!

 

[Bill Booth]

John C, thank you so very much. The formula works perfectly. I will be ready
for my presentation on Monday. You've made my day.
Have a great weekend,

 

[John C]

Thanks for the feedback, and good luck on your presentation!