Here is a simple, yet very powerful, 4-tier equity multiple waterfall module. This post contains both a completed version of the module ready to plug into a real estate financial model as well as a ‘Watch Me Build’ template and companion video where I walk the user step by step through building it out.
EM Waterfall Module Contents
The module gives the user the ability to model partnership cash flow splits up to four tiers using equity multiple hurdles. The contents are contained on one excel sheet where in the top left hand corner is the Promote Structure inputs section where the user dials in all the partnership split assumptions.
Tier 1 inputs contains the initial partnership equity split with an initial hurdle rate that can be dialed in, tier 2 inputs allows the user to input a general partner promote and a tier 2 hurdle rate, tier 3 has the same input options as tier 2, and tier 4 inputs provides the ability to split the remaining profit between the partners according to whatever terms are agreed upon.
I tried my best to make this a short video, but it ended up being my longest one to date at just over 34 mins! In addition to walking you through the model, for about the first 12 minutes I try to explain some basic modeling functions and intuition behind the formulas. After the 12 minute mark, I stop with the excel tips and focus on the model formulas and components only.
Note on formula error in video:
Halfway through the video, I decided to do more stress testing of the model for errors, even though I had already spent a good amount of time doing this. I found one in tier one (row 38), where if I made a cell in the levered cash flow line blank (row 31), the model would break in this row. I had since corrected the formula in the completed version and have a note in the template and completed version as well as below:
Original formula (cell H38):
Updated formula (cell H38)
Download the 4-Tier Equity Multiple Waterfall Model
To make the 4-Tier Equity Multiple Waterfall model accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (Pay $0 if you need) or maximum (your support helps keep the content coming – similar real estate 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 this model (see version notes). Paid contributors to the 4-Tier Equity Multiple Waterfall model receive a new download link via email each time the model is updated.
- Initial launch