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