Hello again, I am working on a 2 investor spreadsheet with 4 different
tiers of distributions and return. One tier of return is exclusively
for the preferred investor (Inv1) which I called "Required Return" and
rest are 1st, 2nd, and 3rd waterfall levels (split levels). In the
following scenario, Inv. 1 must get a 10% Required Return each and
every period starting from period number one. If for some reason, the
cash flow for one or more periods does not come out to the 10% Required
Return, that amount gets carried over to the next period, until the
full amount is paid. For example, during period 1, there was only $50
CF which would amount to a $50 carry-over to the next period. During
period 2, the CF was $90, which gives Inv1 the entire amount and
leaving a carry-over of $10 for a total of $60 carry-over. During
period 3 a CF of $200 was realized which pays off the 10% Required
Return for that period as well as the carry-over amount of $60 and
leaves us with $40 to be split between tier 1 and 2 (1st and 2nd
waterfall levels) . Just to give you an idea, I used the following
information for the tiers:

DISTRIBUTION WATERFALL RATES(AKA SPLIT)
FROM TO INV1 INV2
1st level waterfall 10.00% 13.00% 80.00% 20.00%
2nd level waterfall 13.00% 20.00% 50.00% 50.00%
3rd level waterfall 13.00% 100.00% 80.00% 20.00%



Expected Return Rate for Inv1 = 10%

EXAMPLE:
A B C D E F
13 Period 0 1 2 3 4
14 CF -$1,000 $50 $90 $200 $120
15 Expected Return $100 $100 $100 $100
16 Return to Inv1 $50 $90 $160 $100
17 Inv1 1st waterfall $0 $0 $24 $16
18 Inv2 1st waterfall $0 $0 $6 $4
19 Inv1 2nd waterfall $0 $0 $5 $0
20 Inv2 2nd waterfall $0 $0 $5 $0
21 Inv1 3rd waterfall $0 $0 $0 $0
22 Inv2 3rd waterfall $0 $0 $0 $

Now the real questions...for some reason I cannot correctly write any
formulas that can calculate the following:
If an insufficient CF exists any given period (meaning that it does not
cover the required return, then the CF is return to Inv1 and the
difference between the Expected Return and CF is carried over to the
next period until the entire amount that is carried over is paid. I
must also make sure that cash flow for the consecutive period also
covers the Expected Return for that given period before the carry-over
amount is paid, or else just use that CF for that period to pay as much
as possible. If you look at the example, since the CF in period 1 did
not cover the Expected Return, that entire CF was given to Inv1 and $50
was carried over to the next period. During period 2, the CF was also
not sufficient enough to cover the Expected Return; hence that amount
was also given to Inv1. During period 3, the CF is not only sufficient
to cover the Expected Return but also the carry-over amount of $60 from
previous periods giving Inv1 $160 return and the remainder will be
split between Inv1 and Inv2 using the distribution and split
percentages provided. The CF from period 4 also covers the Expected
Return but only $100 is given to Inv1 as Expected Return and the rest
will be split.
I cannot figure out how to write a formula that does everything that I
need it to do which to figure out if the CF is sufficient...if not
carry-over amount until amount is paid. The formula on the example
above would be from cells C16 through F16. In addition to all that, I
need to also calculate a split of any amount that is left over after
all the Exprect Return and carry over is paid per period as required by
the distribution and split percentages provided for both Inv1 and Inv2.
I calculated the splits and distributions given to both Inv1 and Inv2
for periods 3 and 4 which I hope will explain it a little as to what I
need to do. I know this is very complicated, at least to me so if you
have any questions please feel free to ask.
I would appreciate any input.
Thank you in advance.
Cal