Cash Flow Distribution Modeling

wilson31416 · Nov 20, 2004

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

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

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

Jay Petrulis · Nov 22, 2004

Hi,

Can you provide a sample set of your cashflows and/or dates?

When you are distributing the cash, does the IRR need to be since inception or sequential (i.e. the 18% is calculated from an adjusted beginning flow at the hurdle one time).

I would assume you want a cumulative IRR, but some additional details/sample are necessary.

wilson31416 · Nov 23, 2004

Jay,

A few more details and a correction from my first post are in order. A1 through A36 are a series of cash flows after payment of debt service from a real estate investment. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the II contributes 90% of the required project equity and the developer contributes the remaining 10%. As with all development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows:

1. Both investors receive the return of their invested capital from cash flow distributions.
2. FIRST HURDLE: Both investors then receive a 12% iRR on their invested capital (IRR to be calculated from their respective first dollar invested in the project).
3. SECOND HURDLE: The Institutional Investor then receives all cash flows until an 18% IRR is hit (IRR to be calculated from the Institutional Investor's first dollar invested in the project).
4. FINAL SPLIT: All remaining cash flow is to be equally divided between Institutional Investor and Developer. Once again, the IRR's are to be calculated from project inception.

On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 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

Any thoughts on how to structure this would be most welcome. Thanks for any assistance that you or anyone else can provide.

topazdan · Nov 23, 2004

I, too, am looking for the answer to THIS question.

wilson31416 · Nov 23, 2004

Allow me to clarify even further, as the following points are essential:

1. For Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR.
2. For Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until they reach an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.

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

Jay Petrulis · Nov 25, 2004

Hi,

With n periods, there are n+1 endpoints. From your data, I never hit hurdle one unless I *ex*clude the period 0 cashflow. It never reaches the second hurdle (unless I made a calc mistake or misread the question).

Please advise.

MrExcel CashFlowsIRR.xls