All - I've gotten a couple requests to sketch out/submit a waterfall model that handles various assumptions. Thus attached is a quick and back of envelope approach to a JV development deal. Blue font is input -- I tried to add comments that provide a more detailed explanation of the input and reason for it. What started this was the regular question about multiple equity contributions from more than 1 investor. Please feel free to respond below in the thread with any questions, and I will answer them best I can.
ODDITIES WORTH MENTIONING:
- The only unusual section is T4-V8...where I attempted to calculate the would be splits depending on what the equity contribution is. Meaning, you can tweak the equity contributions without haven't to change the % splits in the hurdle every time. The last term sheet I had used a 10% equity contribution and applied the promote % outlined in cell T6-V6. Row 7, and the total LP splits for each hurdle in Row 8, will automatically update as the equity % is changed for the LP. Of course if you want to do away with this, or I was wrong in the approach, you can just override the blue values in column C & D as you work through the hurdles.
- Preferred Return rate calculation aka compounding vs non-compounding. Select from the drop down box in cell B23...If compounding is selected, select the rate of compounding from cell E23. This will then drive the effective rate the investors will actually receive, utilizing the +EFFECT function.
- Return of Equity: CEll B47 is a drop down that drives the return of equity. Simply, it determines if one wants a return ON equity or a return OF equity. Some term sheets stipulate that equity can only be returned during a capital event. Even if operating cash flow is absolutely gangbusters, no equity will be returned. The standard entry would be "both" which implies that regardless of where the cash flow comes from, if the prior hurdles are paid down and available funds are leftover, it will go to pay down equity. There is also the cash flow only selection, which I have never seen/modeled but heard of...disregard that selection for this model I'd recommend.