Here is another involving waterfalls splits.
Another problem. I have to work on a spreadsheet that has Cash
Flows, IRR, NPV, etc..., and split up the Cash Flow based on the
required rate of return (as the IRR). Sounds easy enough, but if you
are familiar with CFs, you'll realize that the CF could have an
many
combinations of Cash Flow but come out with the same outcome as the
IRR. Example: CF for period 0-6 a -1000, 100, 35, -100, 200, 500,
900 which would give you an IRR of 10% and total CF of $1635. If you
have CF for periods 0-6 of: -1000, 100, -100, -100, 500, 300, 955;
you still come up with an IRR of 10% yet the total CF is now $1655.
First problem: What I need to do is to figure out what the CF will
be based on the IRR. Since we've already recognized that CF could
vary resulting in the same IRR, I want to input CF's for all
period
and have excel calculate what the CF should be during the last
period to achieve a certain IRR.
Second problem: In addition for the last problem, I will also need
to calculate splits based on IRR (and I'm guessing the last CF
period) between two investors. So it would be something like, if
Required Rate of Return is 10% (we'll use the IRR to calculate
Required Rate of Return), and the IRR is 13%, what is the Total Cash
Flow distributions for the Required Rate of Return (basically what
is the sum CFs starting from period 1) if the IRR were at 10%. I
would then have to calculate what the CF would sum up to if we had
an IRR that's between 10% and 13% (split 1). Split 2 would be
between 13% and 20% and split 3 would be 20% and above.
This of course is no easy task since we'll have to rely on the
last
period's CF to get the answer (my first problem). This would be a
3 "waterfall" scenario with a required rate of return; the
required
rate of return being 10%, first waterfall being 10-13 percent,
second waterfall being 13-20 percent, and last waterfall being 20%
and above. The reason we call them waterfalls is because when the
cash flows reach a certain IRR, they spill-over to the next IRR.
Example: assuming we have periods 0-6 CFs using the above waterfalls
and our CF are as follows: -1000, 50, 200, 78, 400, 100, and last
period CF (we don't know this one yet). We want an IRR of 15%.
The
Total Cash Flows with the splits would be as follows:
At 10% IRR we would have a total CF Distribution (sum of periods 1-
6) of $1528
At 10%-13 % (waterfall 1) we would have a total CF Distribution of
$1755-$1528 = $227.

EXAMPLE:
Period 0 1 2 3 4 5 6
CF ($1,000)$50 $200 $78 $400 $100 $1,100
Aggregate Investor IRR = 15.00%

Total Distributions = $1,928 (the sum of period 1-6)

At 13%-20% (waterfall 2) we would have a total CF Distribution of
$1928-1755 = $173.
You'll notice that since we only had a 15% IRR, we did not
completely "fill" up the second waterfall and basically did
not spill anything over to the third waterfall.

Another Example for a 6 period CF using the same splits...If we needed
an IRR of 15%, the CF for periods 0-5 we
Period 0 1 2 3 4 5
CF ($1,000)$-100 $-100 $100 $50 $50

What CF amount would be needed for Period 6 to Achieve an IRR of 15%
and what would the splits be including the amount for the required
rate?
We would need $2414 to achieve an IRR of 15% and the CF would be split
as follows:
Required return based on 10% IRR = $1830 (sum of periods 1-6 to achieve
an IRR of 10%
1st Waterfall $335 (2165-1830=335)
2nd waterfall $249 (2414-2165=249)

The main problem with this spreadsheet it to get the CF to match the
IRR by having the value of only the last CF period change to match the
required IRR.
I know this sounds a little confusing so if you have any questions
please do not hesitate to ask me.
Thank you in advance.
Cal