Real Estate Equity Waterfall Model – IRR and Equity Multiple Hurdles (Updated Sept 2024)

Over the years, this real estate equity waterfall with annual periods and IRR or Equity Multiple hurdles has been one of the most popular models in our library of real estate Excel models. It’s been downloaded thousands of times and we’ve received dozens of suggestions for how to improve the model. As a result, we continue to update and improve the model as we find time and based on the feedback we receive.

Recently, I’ve also been working to update the walkthrough videos that accompany each of my models. In December 2019, I updated the walkthrough video for this model. The video uses v1.5 of the model and incorporates all of the enhancements to-date. You can find the latest version of the model, the updated walkthrough video, and a changelog for the model below.

Notes: v1.94 is a major update that includes modifications to thousands of formulas. This update includes the addition of a fifth tier and the new input to select the number of tiers to include in the analysis. As such, this version may contain errors. If you run into an issue, please send us a message and we’ll roll out a fix.

About this Real Estate Equity Waterfall Model

The model allows for up to four tiers (IRR or equity multiple hurdles). The first tier distributes cash flow to the partners until the LP has achieved some defined preferred return and received a full return of capital. Distributions in the first tier can either be pari-passu and pro rata based on the partners’ proportionate share of equity contribution, or LP first. If the distributions are LP 1st in the 1st tier, the model offers the option for the GP to catch up to the LP.

Assuming the preferred return hurdle is hit in tier one, cash flow is distributed in tiers two through four based on a defined promote structure and hurdle rates as outlined by the user. The hurdles can either be internal rate of return (IRR) or equity multiple hurdles.

If you are needing a less complicated model, you might check out my Real Estate Equity Waterfall Model with One Cash-on-Cash Return Hurdle.

Real Estate Equity Waterfall as a Module

This equity waterfall model is built for inclusion in other models – or in other words it is not standalone. As a result, it assumes you have already modeled the property-level cash flow in your own DCF. With that, you can link the net levered cash flow line from your property-level model to this module.

Most of our real estate DCF models include both a property-level cash flow module as well as a waterfall module (i.e. partnership-level module). In fact, several of the models in our library of real estate Excel models have this exact real estate equity waterfall module built-in.

Dynamic Annual and Monthly Periods

The model is built on an annual and monthly basis for up to 10 years of analysis. The analysis length is fully dynamic – simply link the model to your net property-level cash flow and leave any unneeded periods blank. The model will recognize those periods as unnecessary and hide them from the analysis. You can find a video tutorial on how to adjust hold period further down the page.

Timing of Preferred Return, Return of Capital, Excess Cash Flow, and Promote Distributions

In version 1.7 of the model, I added a section that breaks out the timing of the preferred return, return of capital, excess cash flow, and promote distributions to each of the partners. This allows the user to forecast when certain hurdles are hit, and what share of the total distributions goes to which distribution type.

Since the release of this feature, a user asked why the preferred return distributions occur first followed by the return of capital distributions. You can see my response and a further explanation of the methodology we use by reading this blog post.

Other Elements of the Module to Consider

This waterfall model assumes only one sponsor (i.e. GP) and one LP. And while the model is simple, the foundation is there to easily add additional IRR or Equity Multiple hurdles, periods of analysis, partners, duplicate the worksheet to model a double promote.

Also, with a little more effort, the model can be adjusted to use both equity multiple hurdles and IRR hurdles together.

Video Walkthroughs of the Real Estate Equity Waterfall

I recorded a video walkthrough that includes all of the new features in the model since its release through v1.5. These new features include the addition of the Equity Multiple hurdle, the option to include a GP Catch Up provision, a dynamic hold period of up to 10 years (120 months), and various tweaks and improvements.

Quick Tutorial on How to Adjust Hold Period

In this quick 30 second tutorial, I show you how easy it is to adjust the analysis period. If you’d like to incorporate this concept yourself, take a look at the =IF(XX=””,”” logic that I use at the beginning of each of my formulas. This logic looks to the header row and asks if the header is blank, and if so it will leave that cell blank. Add that logic to each of your cells, and the result is a model fully dynamic to hold period.

Solving for Both IRR and Equity Multiple Hurdles in Version 1.6 of the Model

Per request, in version 1.6 I redesign the structure of the model to allow for the user to solve for and internal rate of return hurdles and equity multiple hurdles. In this video, I share with you how that new feature works.

Download the Real Estate Equity Waterfall Model with IRR and Equity Multiple Hurdles (Annual + 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.

---

A bit of history behind the progression of this model:

• I first built this model with IRR hurdles only, and then followed up with a companion waterfall model using equity multiple hurdles. After concluding the models were redundant, I converted this model into a combined model with both IRR and equity multiple hurdles. You can toggle between IRR hurdles and Equity Multiple hurdles using the drop-down menu in cell D4 of the ‘Partnership Returns – Annual’ tab.
• Another common request we’ve received over the years is to model the waterfall using monthly, rather than annual, periods. Monthly waterfall modeling is common with shorter term, more opportunistic real estate investments. Again, rather than boggin this model down with too many options, I created a separate equity waterfall model with monthly periods.
• In many of my standalone models on A.CRE, I’ve added this equity waterfall model with minor tweaks from one model to the next. For instance, you can find this model included in my Real Estate Portfolio Acquisition Model, as well as in my All-in-One Model.
• In mid-2019 (v1.3 and newer), I added a GP IRR or EMx Catch Up option for scenarios when the LP receives a greater share of the cash flows in the first tier.
• In December 2019, I updated the walkthrough video to incorporate the changes in the model to date.
• In October 2020, in response to several requests from Accelerator members in the forums, I updated the model to allow the user to test for both internal rate of return and equity multiple hurdles in the same analysis. This required a major redesign of the model, and so I created a walkthrough video discussing the changes.
• In April 2021, I added a Monthly version to this model.
• In March 2024 we released v1.94, a major update that includes the addition of a fifth tier as well as the option to select the number of tiers to use in the analysis.

Version Notes

v1.951

• Reverted the logic in C13 such that LP achieves the greater of IRR or Emx scenario, when IRR + EMx is selected

v1.95

• Fixed issue where the IRR + EMx option was taking the greater of, rather than the lesser of, the two options; updated C13 to take the lesser of IRR and EMx
• Fixed link on Version tab
• Update to placeholder values

v1.94 – Mar 19, 2024

• Updated Version tab
• Updated various placeholder values
• Extensive formula improvements
  • Cleaned up hundreds of dynamic (i.e. IF(Row=””,””,)) hold period logic formulas
  • Fixed for consistency 16 Ending Balance formulas which weren’t picking up distribution in time zero (no impact on returns as there are never distributions in time zero)
  • Removed link from ‘Partnership Returns – Monthly’ to ‘Partnership Returns – Annual’ to make exporting the tab easier
  • Added IFERROR() function to D40 (Partnership Returns – Monthly) for scenarios where GP fees exceed GP contribution requirement
• Built 5th tier in each of the four waterfalls (IRR Annual, IRR Monthly, EMx Annual, EMx Monthly)
  • Added Tier 5 Input in row 18
  • Added Tier 5 section to each of the four waterfalls
  • Updated various formulas in Tier 3 and Tier 4 to accommodate 5th tier
• Built ‘# of Tiers’ feature to allow user to select 2 to 5 tiers
  • Added ‘# of Tiers’ dropdown input in row 5
  • Updated Tier assumptions logic based on ‘# of Tiers’ input
  • Updated Tiers logic in each of the four waterfalls to set hurdle on unused tiers to some unattainable to effectively turn those tiers off
• Misc. formatting fixes/updates

v1.93 – Jan 4, 2022

• Fixed issue where IRR calculation on Monthly module (in rows 68, 76, 85, 160, 168, 177) was still using the annual IRR() function, rather than the monthly XIRR() function
• Updated the Error Check formulas in the Monthly module to remove reference to IRR, when unnecessary
• Added OFFSET() function to certain XIRR() functions in Monthly module to make them dynamic to hold period

v1.92 – Sep 7, 2021

• Fixed issue where GP ‘Required Return’ line on ‘Partnership Returns – Monthly’ assumed annual, rather than monthly, periods

v1.91 – Aug 2, 2021

• Fixed issue where GP Catch Up heading had been removed (cell E4)

v1.9 – Jul 7, 2021

• Added ‘Analysis Period’ output in cells D19:E19 of the ‘Partnership Returns – Annual’ tab
• Added ‘Analysis Period’ output in cells D19:E19 of the ‘Partnership Returns – Monthly’ tab
• Rewrote all XIRR formulas on the ‘Partnership Returns – Monthly’ tab; now include the OFFSET() function to make XIRR output dynamic to hold period
• Updated IRR hurdle required return calc to use ACTUAL/365 rather than 30/360

v1.8 – Apr 23, 2021

• Fixed error in summary of preferred return and return of capital cash flows
• Added ‘Partnership Returns – Monthly’ module
  • Duplicated ‘Partnership Returns – Annual’ tab and renamed ‘Monthly’
  • Updated headers to read ‘Month’ instead of ‘Year’
  • Updated Required Return formulas to assume monthly periods
  • Updated IRR return calculations to use XIRR
• Misc. formatting updates

v1.7 – Feb 3, 2021

• Break out Preferred Return, Return of Capital, Promote, and Excess Cash Flow
  • Preferred return total and by period in rows 22 and 32
  • Return of capital total and by period in rows 23 and 33
  • Promote total and by period in row 34
  • Excess Cash Flow total and by period in rows 24 and 35
• Misc. formatting updates

v1.6 – Oct 8, 2020

• Complete redesign of model to accommodate IRR + Equity Multiple hurdle (i.e. the greater of IRR or EMx)
  • Drop-down menu in cell D4 now includes ‘IRR’, ‘Equity Multiple’, and ‘IRR + Emx’; select ‘IRR + Emx’ for LP to receive the higher result of IRR or EMx
  • Duplicated the original waterfall twice
  • Removed the original waterfall
  • Set one duplicate to always calculate waterfall based on IRR
  • Set second duplicate to always calculate waterfall based on equity multiple
  • Added a ‘Hurdle Rate Method Used:” calculation in cell D12; when Promote Hurdle Method (D4) is set to ‘IRR + EMx”, D12 calculates whether IRR or Equity
  • Multiple is higher
  • Changed ‘Summary of Partnership-Level Returns’ section to pull cash flow from either of the waterfalls depending on the value in cell D12
• Revised Conditional Formatting rules to accommodate model redesign
• Extensive formatting updates

v1.51 – Dec 30, 2019

• Minor changes to dummy values in template file
• Various additional changes to labels to add greater consistency
• Added ability to have 0% GP contribution %

v1.5 – Dec 8, 2019

• Updated walk-through video to include all changes since initial release
• Added mini-tutorial videos for modeling GP Catch Up and for modeling GP Fees
• Added new instruction notes and revised existing notes
• Set waterfall calculation section to hide/unhide with one click; set to hidden by default
• Updated print range, and set instruction notes not to print
• Renamed various labels for consistency (e.g. ‘hurdle’ renamed ‘tier’)
• Moved to ‘Accounting’ number format
• Revised header row
• Changed Sponsor to GP for consistency
• Misc. formatting updates

v1.4 – Sept 14, 2019

• Updated to new versions tab
• Added year header above each tier
• Updated to a more modern header design
• Removed blue background on input cells, to be consistent with formatting norm (i.e. formatting best practices)
• Renamed Tier 1 to “Return of Capital & Hurdle 1 (Preferred Return” for better clarity
• Misc. formatting enhancements

v1.3 – May 31, 2019

• 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 %
• Fixed issue where IRR check label was not working properly when using Equity Multiple hurdles
• Fixed issue where Equity Multiple number formatting was returning a percentage instead of 0.00X
• Replaced VLOOKUP() functions with non-volatile formulas

v1.22 – December 10, 2018

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

v1.21 – July 13, 2018

• Added logic to make adjusting hold period more intuitive; simply delete Net Levered Cash Flow from any years not included in the analysis, and those years will no longer appear
• Fixed circular reference in asset management fee input row

v1.20 – June 17, 2018

• Added option to model sponsor fees; can include up to two different sponsor fees (e.g. AUM fee, acquisition/disposition fee)
• 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
• Revised ‘Error Check’ formula to account for rounding errors
• Simplied hurdle 1 modeling; no longer tracking sponsor capital account
• Improved version tracking

v1.10 – December 29, 2017

• Added option to use Equity Multiple hurdles, rather than IRR hurdles (cell D4)
• Miscallaneous bug fixes
• Added link to waterfall module with monthly periods