3 Tiered Waterfall Module Added to the Condo Development Model
After receiving a few requests, I’ve finally gotten around to adding an equity waterfall module to the Condominium Development Model. This module allows you to model partnership splits up to three tiers using either equity multiple hurdles or IRR hurdles. Additionally, you can model for a preferred return for the limited partner where either (1) the general partner receives only its capital back after the LP receives its pref and before moving to the second tier or (2) the GP receives an equal return before moving to the second tier, but after paying the LP the preferred return.
Please download the model below and click on the Waterfall tab to follow along with the remainder of the post and/or watch the video tutorial below.
Model Overview
Once you have the model open and you are in the Waterfall tab, you will notice three sections: the Inputs section (C4:D34), the Return Metrics section (F4:K8), and the Cash Flow section (C37:AQ138). The Inputs section drives the whole module, so this post will focus there. For a more in depth understanding, please watch the video and spend some time reviewing the formulas in the cash flow section. Also, I have tested many scenarios for errors, but it’s always possible that the formulas may not account for something, so let me know if you happen to find any issues while using this and I will correct and update it.
The Inputs Section
IRR or Equity Multiple
The drop down menu in cell D5 is where you will choose whether the waterfall model will use equity multiple (EM) hurdles or IRR hurdles. As you switch between the two scenarios in D5, you will notice that all the cells that are relevant to the scenario NOT selected will be blacked out both in the Inputs section and the Cash Flow section. To clarify, let’s look at Tier 1 in the Cash Flow section (rows 42:77). If we select IRR in the drop down menu in D5, rows 45:46 and rows 58:59 are blacked out. These are the rows that are used for calculating returns based on an equity multiple. If we then select EM in cell D5, rows 45:46 and rows 58:59 are now open and rows 47:54 and rows 60:67 are blacked out.
Partnership Equity Contribution
In cell D7, simply type the percent of the equity required that will be paid out by the LP and the remainder will be allocated to the GP. Negative cash outflows during the investment period will be allocated in the Cash Flow section accordingly. To verify, see row 43 for any negative cash flows and then look to row 56 for the LP portion and row 69 for the GP portion. The negative cash flow in row 43 should be split in rows 56 and 69 according to your input in cell D7 for the LP and D9 for the GP.
Tier 1
Use either cell D13 or D14, depending on whether you are using an IRR or EM hurdle, to enter in your first hurdle rate. In cell D15, the model asks if there is a preferred return. If you select ‘No’, then cells C17:D19 will open up. Using cell D18, choose what percent of the positive cash flow in Tier 1 will be allocated to the LP until the first hurdle is reached. If you select ‘Yes’ in cell D15, cells C16:D16 open up and cell C16 asks if the GP will ‘get an equal return on cash before moving into the next tier or only a return of capital’. Choosing ‘Return Capital Only’ in cell D16 will do just that for the GP after the LP has received its pref and if there is enough cash available. All remaining available cash, if any, will be disbursed throughout the next two tiers. Selecting ‘Equal Return’ in cell D16, the model will disburse the LP’s capital and pref first and then if there is enough cash available, the GP receives a return of capital and profit up until the GP and LP have an equal return before moving to the next tier.
Tiers 2 and 3
Use either cell D23 or D24, depending on whether you are using an IRR or EM hurdle, to enter in your second hurdle rate. In cell D29, you enter in the GP’s tier 2 promote. Cells D28 and D29 are added together to equal the total percent of profit in tier 2 that will be allocated to the GP (Cell D27) until the LP reaches its second hurdle rate. If there is any remaining cash flow to disburse once the LP hits the second hurdle rate, the remaining cash will be split according to cells D33 and D34 to which cell D34 can be manually adjusted and D33 will alter accordingly.
Video
For more details and direction, please watch the video:
Download the Condominium Development Model with Waterfall Module
To make this model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical real estate development models sell for $100 – $300+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.
We regularly update the model. Paid contributors to the model receive a new download link via email each time the model is updated.
