Waterfall Model

A waterfall financial model can be used with any of the financial models I have built previously. The logic is probably the most difficult out there to understand, but it is widely used in joint venture real estate deals.

Waterfall Distributions: Version 1



Pricing
*You will be taken to the template download page after purchase. And, the purchase of this model includes a total of 4 template types. (V1, V2, Sensitivity version and my original joint venture real estate template).

Waterfall with Sensitivity (5 scenarios) - Model Included With the One-Time Purchase Above




Waterfall Distributions V2 (both versions are included in the one-time purchase price of $45)


This specific template uses a standard real estate proforma (monthly/annual summaries) in order to come up with cash flows from Month 0 to Month x (up to 240 months). All you really need to do though is get a proper cash flow flowing to row 49 of the 'monthly pro forma' tab and the waterfall will work. For this reason, any of the previous models can be easily integrated.

There are assumptions used to populate the real estate proforma in this template:
  • Up to 20 years
  • Start year
  • Month rent starts
  • Starting monthly rent
  • Annual rent growth
  • Vacancy
  • Plenty of rows for various operating expenses, including property tax/insurance.
  • Debt service (dynamic loan)
  • Exit value based on an exit cap
  • Exit month (all cash flows stop at the exit month)
    • At exit month the loan is assumed to get paid off if it has a balance
Next, we have the waterfall tab with up to 3 hurdles and a final split.

With the hurdle tab, you can define the initial % split or base returns that are given to the investor and sponsor before any IRR hurdle is met.

Then, you can add up to 4 IRR rates. The first one will define when the splits begin to change and so on until you get to the final hurdle. 

The logic for the waterfall is as follows:
  • As cash comes in each month, the IRR for the investor and sponsor starts to change.
  • Every time a new hurdle is reached, based on the previous cash flows (that may be split from previous hurdles), only the cash flow that has not yet been split are used to flow to the new % splits.
  • Any cash flow that is received beyond the final hurdle % will get split by the final split, which could be 50/50 or whatever you want to make it. In that case, the sponsor would have achieved the top hurdle rate and then some.
With this logic, you end up with some cash flows being split at the base rate, then some at the Hurdle 1 rate, Hurdle 2 rate, and Hurdle 3 rate. So the final % of total cash flows that the sponsor/investor gets will always be a variant based on how much cash was split at each level.

I added a chart and 'return summary' tab that clearly shows how the hurdles affect the total cash flow returned. Note that the 'final split' is what the actual cash flows will be after all hurdles were looked at and will represent the aggregate of cash flowing through all hurdles where applicable.