Waterfall cashflow distribution
 

Here's another cashflow problem. And it has two parts.

Part 1. The Limited Partner (LP) and General Partner (GP) make
contributions on a 90%/10% ratio. Here's an example cashflow. "Year" is
A1.

A B C D
Year LP GP NCF
0 -900 -100 -1,000
1 0 0 50
2 0 0 175
3 0 0 170
4 0 0 180
5 0 0 185
6 0 0 190
7 0 0 200
8 0 0 205
9 0 0 210
10 0 0 215


Here's the order of distribution:
1. LP receives its preferred rate of 8%
2. GP receives a preferred rate of 8%
3. LP and GP capital is returned para passu (90%/10% respectively)
4. 80% to the LP and 20% to the GP until the LP has achieved a 10% IRR
5. 70% to the LP and 30% to the GP until the LP has achieved a 12% IRR
6. 60% to the LP and 40% to the GP until the LP has achieved a 14% IRR
7. 50% to the LP and 50% to the GP thereafter

I have capital accounts and preferred return accounts for both
partners, so I figured out numbers 1 thru 3. But how do I calculate the
distributions for numbers 4 thru 7? See, the difficulty is that the IRR
hurdle is the LP's IRR, not the Project's Total IRR.

Part 2. If that wasn't difficult enough, let's assume the project is
real estate. The property can be sold in any year. Here are the sale
proceeds (column E):

SaleCF
0
1,000
3,500
3,400
3,600
3,700
3,800
4,000
4,100
4,200
4,300

I've attempted this by using a matrix like this:

Blank YrSold0 YrSold1 YrSold2 YrSold3 YrSold4 …
OperYr0 -1,000 -1,000 -1,000 -1,000 -1,000 …
OperYr1 0 1,050 50 50 50 …
OperYr2 0 0 3,675 175 175 …
OperYr3 0 0 0 3,570 170 …
OperYr4 0 0 0 0 3,780 …
OperYr5 0 0 0 0 0 …
OperYr6 0 0 0 0 0 …
OperYr7 0 0 0 0 0 …
OperYr8 0 0 0 0 0 …
OperYr9 0 0 0 0 0 …
OperYr10 0 0 0 0 0 …

Honestly, if you can show me an elegant way to solve Part 1, I think I
can figure out Part 2.

I do have a solution to this, but it ain't pretty or elegant. If fact,
it takes 376 rows and 13 columns, and adds 1/2 mg at least to my
spreadsheet. But it involves finding the FV of the NPV of
contributions and distributions.


--
TXdore
------------------------------------------------------------------------
TXdore's Profile: http://www.excelforum.com/member.php...o&userid=20803
View this thread: http://www.excelforum.com/showthread...hreadid=507000