Hi Cal

Maybe I have lost track as to where on your sheet you work. Give me the
cell references, as I did in a previous post to you. I can then write the
formulae so that you can just copy them off the post into your sheet, without
having to make any alterations.

Also. I need to know how you split the surplus funds between the various
waterfalls. As I said, clearly you used a 75%/25% split in the example.
This would however mean that you will never be able to split into a 3rd
waterfall?

Also, please elaborate on the 10%-13% issue?
--
ve_2nd_at. Randburg, Gauteng, South Africa


"Kassie" wrote:

Hi Cal

I now have a better understanding of the waterfall issue, however, still not
clear how you decide to give 10% or 13% ("the first waterfall is 10% - 13%"),
13% or 20% and so on. Once I have grasped that, I am pretty sure we can do
it.

However, I am concerned that you say the helper rows did not help? Have you
used my formulae as suggested?
--
ve_2nd_at. Randburg, Gauteng, South Africa


" wrote:

Thank you for replying. The splits are based on the CF that is
received. For example, if the initial investment is $1000, the required
return by the preferred investor is 10%, the first waterfall is 10%-13%
with a split of 80% preferred investor/20% secondary investor (% of
total return for period 1), second waterfall is 13%-total CF, and the
first CF was $150, then the preferred investor would automatically
receive his $100 share ($1000*10%). The remaining $50 would get split
into two different wateralls, 10%-13% of the CF which would give us an
amount of $30 that will be split into 80% preferred investor
($30*80%=$24) and 20% for secondary investor ($30*20%=$6). From here,
the remaining $20 or 13%to total CF, would be split into 50% preferred
investor or ($20*50%=$10), and 50% secondary investor ($20*50%=$10).
That gives us the total amount of $150 CF for period one. This must be
done for every period so if the CF for all periods were the same
($150), then the preferred investor would always get $134
($100+$24+$10) and the secondary investor would always get $16($6+$10)
With this being said, I also have another problem that I must deal
with. If for example during the first period the CF does not come out
to the required rate of return by the preferred investor, then that
amount gets carried over to the next period until the required return
has been satisfied. So if the CF for first period was $80, the
preferred investor would the entire amount. However, $20 will get
carried over to the next period. So if the CF for period two was $130,
then the preferred investor would get $120 ($100 for required rate and
$20 that was carried over from period one) to fulfill his required
return, and the remaining $10 would get split 80/20, $8 for preferred
investor and $2 for secondary investor, since it falls into the 10%-13%
return. That gives us the total CF for period two of $120+$8+$2=$130.
If however the CF for period two does not meet the required return,
then that amount also gets carried over. So if period two also only had
a CF of $80, then $40 more will need to paid to the preferred investor
before moving down to the splits which makes it even more confusing.
This type of scenerio could go on for upto 10 periods where the
required return is less than the preferred rate of return during the
first 9 CF, then the 10th CF could be 3-4 times the initial investor in
which case all the required returns and carry-over amounts will still
need to be paid before waterfalls and the splits.
I tried helper rows but I am truely stumpped as to what to do. I must
have tried several dozen different ways of writing formulas and helper
rows, yet I still cannot come up with a formula or a method of
automatically calculating this type of scenerio. I would really
appreciate any suggestions you could provide.
Thanks again,
Cal