I’ve just wrapped up a new waterfall model over the weekend that I am excited to share on the site. This model was built as an addition to the acquisitions model I posted a few weeks back. And like the acquisition model, I made an attempt to make this as intuitive as possible for the user, aesthetically pleasing, and easy for the eye to follow. Also like the acquisition model, this entire waterfall model is on one sheet and the user can operate and read it from left to right. It is broken into three main sections: Inputs (left), the Waterfall Model (center), and the Return Summary (right).
The model gives you the ability to project up to three hurdle rates (tiers) and it has an option to include a ‘catch up’ for the sponsor between the first and second tier. In Tier 1, the user can model for either a pari passu split or for a preferred return for the LPs.
Waterfall with Catch Up Option
The optional Catch Up section, which is positioned under tier 1 in all three sections of the model, is currently ‘turned off’ when you download the model, but can be turned on by answering ‘yes’ to the question ‘Is there a catch up?’ in cell D32. If you are using the catch up section, you must use solver or goal seek to calibrate the waterfall model any time you alter assumptions in your DCF model or in the sponsor fees, equity split, or tier 1 sections of the waterfall (these are all inputs that come before the catch up section). This is explained fairly thoroughly in the video below and there are also explicit instruction on how to use both solver and goal seek with the model written under the inputs section. Additionally, as an added safety, there are three warning signs placed strategically throughout the model that will remind you to use solver if required. If you are unfamiliar with catch up clauses and how they work, it is explained in the attached video below and will also be laid out in a follow up post.
Get proficient in real estate financial modeling with REFM's two-day intensive training course in NYC (or attend online) February 11th and 12th. Click to learn more (Paid)
One final note, the leveraged cash flow coming in from the DCF model can be split between one or two limited partners and the sponsor, but a user should be able to build upon this model to have it account for more partners if necessary. Also, with a little tweaking of some of the formulas, it is possible to make this model work with any DCF model that calculates returns and payouts annually.
Download The Model
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 models sell for $100 - $300 each). 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.
Note (especially for Gmail users): The model is sent via email and occasionally is blocked by spam filters. If you don't see the email arrive within five minutes, check your spam folder.
Real Estate JV Waterfall Model
- 3 tier promote structure
- Catch up clause between tiers 1 and 2
- Breakdown LP returns between two LPs
- Optional preferred return or pari passu split
This Excel file (the "software") is distributed as is, completely without warranty or service support. www.AdventuresinCRE.com and its authors are not liable for the condition or performance of the software. www.AdventuresinCRE.com owns the copyright and grants users a perpetual, irrevocable, worldwide, non-exclusive, royalty-free license with respect to the software as set forth below.
www.AdventuresinCRE.com and its authors hereby disclaim all implied warranties. www.AdventuresinCRE.com grants the users the right to modify, copy, and redistribute the software and documentation, both within the user's organization and externally, subject to the following restrictions:
1. The users agree not to charge others for use of the software, regardless of whether users have altered and/or improved the software in anyway.
2. In any use of the software, the users agree to acknowledge the www.AdventuresinCRE.com authors that developed the software.
3. The users agree to obey all government restrictions governing redistribution or export of the software.
4. The users agree to reproduce any copyright notice which appears on the software and documentation on any copy or modification of such made available to others.
For more information and general guidance on using the model, please watch the video below:
I hope our readers find this model useful. Again if there are any questions, concerns, or general feedback, feel free to contact me.
About the Author: Michael Belasco has over eight years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.