Monthly Waterfall Distribution - IRR Hurdles - Sensitivity of up to 5
Video Overview Version 1:
Video Overview - Version 2:
Video Overview - Version 3 (sensitivity for V1)
The monthly waterfall financial models can be used in conjunction, as well as compliment, any other financial model. They are especially applicable in joint venture, real estate deals but anything that outputs a cash flow (positive or negative) per month will fit. Three models are included in the purchase: Waterfall Distribution- Version 1, Version 2, and 5-scenario with Sensitivity.
In the Waterfall Distribution - Version 1, standard real estate proforma is used, with monthly and annual summaries, which calculates cash flows from month 0 to month x (≤240 months). To make the waterfall work, all users have to do is input proper cash flow until row 49 of the “monthly pro forma” tab.
Certain assumptions are used to populate the real estate proforma, such as: the start year, 20-year span, starting monthly rent, month rent starts, annual rent growth, vacancy, operating expenses (including property tax and insurance), debt service, exit value (based on exit cap), and exit month. At exit month, all cash flows stop and any remaining loan balance is assumed to be paid off.
A waterfall tab is included with up to three hurdles and a final split. Users can define the initial percentage split. They can also define base returns given to limited and general partners prior to any IRR hurdle being met.
Four IRR rates can be added. The first of which will define when splits change, and so on, until the final hurdle is achieved.
The logic is as follows:
Cash inflow occurs monthly and the IRR for the limited and general partners start to change. 🡪 Hurdles are reached based on previous cash flows, taking into account splits from previous hurdles, and only remaining cash flow that has not been split it used to flow to new percentage splits. 🡪 Cash flow received beyond the final hurdle percentage will be split, which is specified by the user, such as 50/50. In a 50/50 split, the general partner would have achieved the top hurdle rate, plus some.
This logic results in some cash flow split at the defined base rate, some at the hurdle 1 rate, hurdle 2 rate, and hurdle 3 rate. The final percentage of total cash flow that the limited and general partner receive will vary based on how much cash is split at each level.
Visuals and a “Return Summary” tab are included for transparency, which show how hurdles affect total cash flow returned.
In the Waterfall Distribution - Version 2, the input is only for monthly cash in/out and the IRR hurdle rates (only two hurdles in this one). The new logic is that the user can account for two investor groups instead of just a single investor and a sponsor. The investor groups have the same hurdles, but can split cash differently as the hurdles are achieved.
In the Waterfall Distribution - Version 3, there are inputs structured just like in version 1, except there are now up to 5 different scenarios that can be run based on varying real estate acquisition price/operation/exit assumptions. The IRR Hurdles work between a single sponsor and investor just like in version 1.
Note: I am not a financial advisor and this is not financial advice. Use this template at your own risk.
The delivery of these 3 templates will be in the form of a google download link, which lets the user download the 3 files into Excel. As long as you can open a Microsoft Word document, all three templates are accessible.
