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.

Video Contents

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):

=IF(H31<0,H31,MIN(H31,$C$35-SUM($G$38:G38)))

Updated formula (cell H38)

=IF(H31=””,0,IF(H31<0,H31,MIN(H31,$C$35-SUM($G$38:G38))))

Video

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.

Template

Blank and formatted template to use with video.

 
   
By clicking Continue, I agree to the following Terms & Conditions.
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.

AdventuresinCRE.com will not sell or distribute your email address to third parties, but you understand AdventuresinCRE.com and/or its direct affiliates may email you from time to time including an initial email containing a link to download the aforementioned Excel file.

Completed Version

Equity Waterfall Module - Completed Template

  • Complete and ready to plug in to a financial model
  • Use as a guide with the video
 
   
By clicking Continue, I agree to the following Terms & Conditions.
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.

AdventuresinCRE.com will not sell or distribute your email address to third parties, but you understand AdventuresinCRE.com and/or its direct affiliates may email you from time to time including an initial email containing a link to download the aforementioned Excel file.

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.