Real Estate Equity Waterfall Model - Monthly Periods (Updated Jul 2021)

When I decided to share my real estate equity waterfall model in Excel, I never imagined it would be as popular as it has. The model has been downloaded thousands times and many of you have written to extend your thanks for sharing. It is gratifying to know that the model has been put to good use and we welcome the opportunity to give back to the commercial real estate community in any way we can.

Note: In July 2021, this model was combined with my Real Estate Equity Waterfall Model – IRR and Equity Multiple Hurdles model. This particular module makes up the monthly cash flow in that model and is the ‘Partnership Returns – Monthly’ worksheet. Refer to that model for all updates going forward.

Are you an Accelerator Member? Check out the Advanced Concept Module on how to add this feature to your waterfall model. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.

The genesis of this model, is that I received a nice email from someone requesting that I modify my equity waterfall model to include monthly, rather than annual, cash flow periods. The modification took a bit of work, but you can find a link to download the model at the end of this post.

This model uses the same general methodology as my other equity waterfall model, only this version uses monthly rather than annual periods with the option to calculate IRR hurdles using either XIRR() or IRR()*12 . Unlike its annual counterpart, it does not have an option to use Equity Multiple hurdles nor does it include the option to model asset management fees or development fees. As time permits, I’ll work to add those features to this model.

Note: Are you an Accelerator member? Learn about the distinction between XIRR() and IRR()*12 as well as how to build an equity waterfall model with monthly periods by clicking here.

The model also breaks out the cash flow distributions to each of the partners. The distribution types include preferred return, return of capital, excess cash flow, and promote. Additionally as of v1.6, the model calculates GP fees both before and after fees.

Using the Equity Waterfall Model with Monthly Periods

Using the model is simple. Just drop in your analysis start date, net levered cash flow stream from your DCF, sponsor vs. partner equity share, preferred return and IRR hurdles, choose whether to calculate IRR hurdles using XIRR() or IRR()*12, and then let the model do the rest. And again, it is not intended to be a standalone model, but it should be rather simple to plug into your own model.

The model has been available for several years and gone through several updates. Nonetheless, there may be errors so please let me know if you find any. And as always, feel free to reach out with any comments or questions you may have.

Download the Real Estate Equity Waterfall Model with IRR Hurdles (Monthly Periods)

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 – similar real estate equity waterfall 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 (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.

Proceed to Download Page

Version Notes

Version 1.7 – July 27, 2021

Combined with Real Estate Equity Waterfall Model – IRR and Equity Multiple Hurdles; that model now includes an Annual and Monthly option with this model making up the monthly option

Version 1.7 – January 30, 2021

Added breakout of distributions by period
- Breakdown of preferred return cash flow to LP and Sponsor (rows 20 and 30)
- Breakdown of return of capital between LP and Sponsor (rows 21 and 31)
- Breakdown of excess cash flow to LP and Sponsor after payment of promote to Sponsor (rows 22 and 32)
- Calculate amount of promote paid to Sponsor (row 33)
Added Frozen column to make analyzing cash flow periods easier
Misc. updates to template inputs

Version 1.6 – June 16, 2020

Added breakout of distributions
- Breakdown of preferred return cash flow to LP and Sponsor
- Breakdown of return of capital between LP and Sponsor
- Breakdown of excess cash flow to LP and Sponsor after payment of promote to Sponsor
- Calculate amount of promote paid to Sponsor
- Calculate and display breakdown of cash flow for each type between LP and Sponsor
Now calculating Sponsor returns before and after fees
Misc. formatting updates

Version 1.5 -Sept 14, 2019

Updated to a more modern header design
Removed blue background on input cells, to be consistent with formatting norm (i.e. formatting best practices)
Added option to model GP IRR and Equity Multiple Catch Up
- Added drop-down menu to use GP Catch Up or not
- Catch Up option appears when GP’s ownership share is >0% but it’s distribution % in tier 1 is less than it’s pro rata ownership share %
Misc. formatting enhancements

Version 1.4

Updates to Version tab
Added option to calculate IRR hurdle using XIRR() or IRR()*12
- Inserted drop-down menu to toggle between two calculation methods
- Updated Required Return calculation rows and IRR Check formulas to accommodate both methods
Recorded new walk-through video for this model
Updated Sponsor return calculations to accommodate scenario where Sponsor contributes 0
Misc. formatting fixes and improvements

Version 1.3

Revised ‘Error Check’ formula to account for rounding errors
Fixed an error where in some cases the 2nd tier LP distributions were overestimated
Added option to model sponsor fees; can include up to two different sponsor fees (e.g. AUM fee, acquisition/disposition fee)
Changed analysis start date assumption

Version 1.22

Cleaned up Promote Structure inputs and notes to make it more clear how Sponsor is being promoted

Version 1.2

Removed reference to USD to better support non-US users
Renamed tab to ‘Partnership Returns – Annual’, to better reflect purpose of module
Various formatting improvements
Simplied hurdle 1 modeling; no longer tracking sponsor capital account
Improved version tracking

Version 1.1

Miscellaneous bug fixes
Added link to waterfall module with annual periods

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.

Contact Spencer