Cash Flow Distribution/IRR Modeling

[wilson31416]

I am trying to model the distribution of cash flows from a real estat
investment. This is a bit complicated, but basically there are a serie
of cash flows from month 0 (closing) to month 36. The "waterfall" (orde
of priority in return of and return on invested equity capital) is a
follows:

1. Both equity investors receive return of their invested capital fro
cash flows.
2. Investor 1 then receives 90% of every dollar from cash flow unti
BOTH equity investors achieve a 12% IRR (12% IRR = "First Hurdle").
3. First investor then receives 60% of every dollar after First Hurdl
is met until first investor achieves an 18% IRR ("Second Hurdle").
4. First Investor then receives 40% of all remaining cash flow. afte
Second Hurdle is met.

I have a worksheet set up that shows hurdle rates, equity invested, IR
calculations, etc., but can't display it here because of a problem wit
the HTML Maker applioation (and there are probably numerous error
contained in the spreadsheet). If anyone has any suggestions on how t
structure this cash distribution waterfall, (or would like to see th
worksheet) then please let me know. Thanks.
_________________
Wilson3141

 

[wilson31416]

Here's a better synopsis of the issues:

I am trying to model the distribution of cash flows from a real estat
investment. This is a bit complicated, but basically A1 through A37 ar
a series of cash flows after payment of debt service from month
(closing) to month 36. The real estate project has two investors, th
Institutional Investor and the Developer. In this case, th
Institutional Investor contributes 90% of the required project equit
and the developer contributes the remaining 10%. The cash flow i
distributed according to a set of parameters specifying cash flo
percentage allocations and IRR targets, collectively known as a "cas
flow waterfall"." As with all for-sale residential development deals
the early cash flows are negative and then turn positive as unit sale
occur. The order of distribution of the cash flow remaining after deb
service is paid is as follows (the waterfall):

1. Both investors receive the return of their invested capital fro
cash flow distributions.
1. Hurdle 1: After the return of equity to both investors, th
Institutional Investor receives 90% of the monthly cash flows and th
Developer 10%, proportional to their respective overall equit
contributions, until both investors achieve a 12% IRR (all IRR's to b
calculated from deal inception/first dollar invested).
2. Hurdle 2: After Hurdle 1 is satisfied, the Institutional Investo
receives 60% of the monthly cash flows until the Institutional Investo
achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cas
flow 50% to each investor.

On an overall project basis, the Developer should realize
disproportionate share of the IRR after the second hurdle is hit. Thi
is what is known as the "promote." The most recent 36 month (3
including Closing) projected cash flows are as follows:

($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
$107,443
$105,853
$121,681
$122,583
$126,513
$129,555
$137,505
$141,510
$66,585
$82,955
$122,352
$197,911
$221,962
$266,292
$255,752
$268,942
$263,621
$268,470
$267,940
$269,958
$259,368
$278,868
$287,375
$296,360
$13,000
$4,270
$4,270 - Month 36

I realize this is complicated, but this structure is quite common t
real estate, private equity, venture capital and hedge fund investment
(along with most any joint venture deals with multiple classes of equit
participants). The canned software that is out there that performs thi
type of anlysis is both expensive and rigid, and doesn't typicall
enable the required sensitivity analysis in structuring a deal. An
feedback/solutions would be most appreciated. Thanks

 

[LeninVMS]

Wilson,

But Taking all the 37 values, the IRR turns out to be 5% under a 90-1
split, in which case, the question of hitting the first hurdle does no
arise.

I might be off the mark here. Can you throw more light?

Regards,
Leni

 

[wilson31416]

Lenin,

First, note that the figures I presented are subject to substantia
revision. My goal is to structure a model that enables sensitivit
analysis for all variable inputs (cash flows, hurdle rates, investo
contribution percentages, etc.), so don't focus too much on the actua
figures. As a matter of fact, change the closing period cash flow to
significantly smaller outflow (say ($500,000 or so) for purposes o
illustration. Second, The 90/10 split applies to the project equit
contributed by the Institutional Investor and the Developer
respectively. The 90/10 ratio also applies to the allocation of cas
flows only until the First Hurdle rate of 12% is achieved by bot
investors (which will happen at the same time because the 90/10 rati
has remained constant up until that point). When the Second Hurdle i
reached, the Developer will have received 40% of the cash flow afte
the First Hurdle is reached, representing a disproportionate share vs
the initial 10% equity contribution. The final split is even mor
pronounced in terms of the "inequality" in cash flow allocations, a
the Developer receives half of the remaining cash flow after the Secon
Hurdle is hit. That is the appeal of developing property (especially ra
land): the Developer can achieve very substantial returns (70%-120
pre-tax IRR's in some cases) on a successful project due to th
outsized gains after the First or Second Hurdle that the typical dea
structure affords him or her.

I hope this answers your questions. Again, any input/proposed solution
would be helpful

 

[LeninVMS]

Wilson:

Let me know if this works for you:

B1=90% (for II)
C1=B1*A1
D1==IF(ISERROR(IRR($C$1:C1,0.1)),-1,IRR($C$1:C1,0.1)) (gives the irr
till the current cash flow for II)

B2=if(D1>12%,60%,90%)
and copy cells C1,B2 & D1 down.

You can extend the formula in B2 to accomodate the second hurdle rate.

Regards,
Lenin

 

[wilson31416]

Lenin,

Two things:

1. The first thing that must be done is to track the equity outstandin
at the beginning and end of every month.

A1 = project cash flow for month
A2 = beginning of month equity balance (0 in Month 1)
A3 = equity advance, which will be the value contained in the first ro
if negative
A4 = equity at end of month, which is the sum of the two rows abov
it
A5 = cash flow after return of equity, which is the sum of the cas
flow for the month and the equity advance for the month (A1+A3)

B2 = beginning of month equity balance for month 2, which is the valu
contained in cell A4

etc, etc.

2. The IRR calculations need to target exactly the stated Hurdle Rat
(12% is First Hurdle in this case). I think that the formula you wrot
will give the IRR at the end of each month, while I need to include onl
those cash flows (from inception/first dollar invested) that will yiel
exactly the specified IRR.

Does this make sense

 

[Dave]

Would someone be able to forward me a waterfall structure similar to what
Wilson described?

Thanks in advance for any help you can offer.

 

[Dave]

Would someone be able to forward me a waterfall structure similar to what
Wilson described?

Thanks in advance for any help you can offer.