For years, the real estate equity waterfall model, particularly one that incorporates annual periods and Internal Rate of Return (IRR) or Equity Multiple hurdles, has stood as a cornerstone tool within our collection of real estate Excel models at Income-Partners.net. Its widespread popularity is evident in thousands of downloads and the valuable user feedback that continually drives its refinement. We are committed to enhancing this model, integrating user suggestions and our own innovations to ensure it remains a cutting-edge resource.
In line with this commitment to improvement, we recently updated the video walkthroughs accompanying our models. As of December 2019, a new video tutorial is available for this equity waterfall model, showcasing version 1.5 and all the enhancements implemented up to that point. Below, you’ll find access to the latest model version, the updated video walkthrough, and a comprehensive changelog detailing the model’s evolution.
Note: Version 1.94 represents a significant update, involving modifications to thousands of formulas. This version introduces a fifth tier and a new input for selecting the number of tiers used in the analysis. Given the extensive changes, we advise users to report any issues encountered via our contact form, allowing us to promptly address and resolve them.
Understanding the Real Estate Equity Waterfall Model
This sophisticated model is designed to handle up to five tiers within an equity waterfall structure, accommodating both IRR and equity multiple hurdles. The primary function of the first tier is to distribute cash flow to partners until the Limited Partner (LP) achieves a predetermined preferred return and receives a full return of their initial capital. Distributions in this initial tier can be structured in two ways: either pari-passu and pro rata, based on each partner’s equity contribution percentage, or with the LP receiving distributions first. In scenarios where distributions are LP-first in the initial tier, the model provides an option for the General Partner (GP) to subsequently “catch up” to the LP’s level of return.
Once the preferred return hurdle in the first tier is reached, subsequent cash flow distributions, spanning tiers two through five, are governed by a defined promote structure and hurdle rates, customizable by the user. These hurdles can be defined using either Internal Rate of Return (IRR) or equity multiple metrics, offering flexibility to suit different investment strategies and partnership agreements.
For users seeking a simpler model, we also offer a Real Estate Equity Waterfall Model with One Cash-on-Cash Return Hurdle, which may be more appropriate for less complex scenarios.
Integration as a Module within Larger Models
This equity waterfall model is specifically engineered to function as a module within larger, more comprehensive financial models. It is not intended for standalone use. Therefore, it assumes that users have already developed a property-level cash flow projection, typically within a Discounted Cash Flow (DCF) model. Users can seamlessly integrate this waterfall module by linking the net levered cash flow line from their property-level model directly into this module.
Many of our real estate DCF models already incorporate both a property-level cash flow module and an equity waterfall module, representing the partnership-level dynamics. In fact, several models in our library of real estate Excel models feature this exact real estate equity waterfall module as an integral component.
Dynamic Period Analysis: Annual and Monthly
The model is built to operate on both annual and monthly periods, extending up to a 10-year analysis timeframe. The analysis duration is fully dynamic; users simply link the model to their net property-level cash flow and leave any superfluous periods blank. The model intelligently recognizes these blank periods and automatically excludes them from the analysis, streamlining the output and focusing on relevant data. Video tutorials are available further down this page demonstrating how to adjust the hold period dynamically.
Detailed Timing of Distributions: Preferred Return, Capital Return, Excess Cash Flow, and Promote
Version 1.7 of this model introduced a significant enhancement: a detailed breakdown of the timing for preferred return distributions, return of capital, excess cash flow distributions, and promote distributions to each partner. This feature provides users with valuable insights into when specific hurdles are achieved and the allocation of total distributions across different distribution types for each partner.
Following the implementation of this feature, a user inquired about the sequential order of distributions, specifically why preferred return distributions precede return of capital. For a detailed explanation of the methodology behind this approach, please refer to our blog post discussing the timing of preferred return versus return of capital in equity waterfalls with IRR hurdles.
Key Considerations and Model Extensibility
This equity waterfall model is structured with the assumption of a single sponsor (GP) and one Limited Partner (LP). While designed for simplicity and clarity, the foundational structure is robust and adaptable. Users can readily expand the model to include additional IRR or Equity Multiple hurdles, extend the analysis period, incorporate more partners, or even duplicate the worksheet to model more complex scenarios, such as a double promote structure.
Furthermore, with some modification, the model can be adapted to utilize both equity multiple hurdles and IRR hurdles concurrently within the same analysis, offering even greater flexibility for sophisticated deal structuring.
Quick Assistance: For users who prefer not to conduct DIY analysis, A.CRE Consulting offers bespoke modeling services to handle your specific project requirements.
equity-split
Video Tutorials: Mastering the Real Estate Equity Waterfall
Comprehensive video walkthroughs are available, covering all the features added to the model since its initial release up to version 1.5. These tutorials delve into new functionalities such as the Equity Multiple hurdle, the GP Catch Up provision, dynamic hold periods up to 10 years (120 months), and various other refinements and improvements.
Quick Guide: Adjusting the Hold Period
This concise 30-second tutorial demonstrates the ease of adjusting the analysis period within the model. For users interested in implementing this dynamic hold period functionality in their own models, examine the =IF(XX=””,”” logic used at the beginning of many formulas. This logic checks the header row for blank cells; if a header is blank, the corresponding cell is also left blank. Integrating this logic throughout your model results in a hold period that is fully responsive and dynamic.
Solving for IRR and Equity Multiple Hurdles Simultaneously in Version 1.6
Version 1.6 of the model introduced a redesigned structure allowing users to solve for both internal rate of return hurdles and equity multiple hurdles within a single analysis. This video tutorial explains the mechanics of this new feature and how to effectively utilize it.
Download the Real Estate Equity Waterfall Model with IRR and Equity Multiple Hurdles (Annual + Monthly Periods)
To ensure accessibility for all users, this model is offered on a “Pay What You’re Able” basis, with no minimum price (enter $0 if desired) and no maximum. Your contributions support the ongoing development and maintenance of our content – comparable real estate equity waterfall models are often priced between $100 and $300+ per license. Simply enter your chosen price along with your email address to receive the download link, then click ‘Continue’. For any questions regarding our “Pay What You’re Able” program or the rationale behind offering our models in this manner, please contact either Mike or Spencer.
Model updates are released regularly (version notes). Paid contributors automatically receive a new download link via email whenever an updated model version is released.
Proceed to Download Page
Model Evolution: A Historical Perspective
- The initial iteration of this model featured only IRR hurdles, followed by a separate companion model using equity multiple hurdles. Recognizing the redundancy, the models were merged into a combined model accommodating both IRR and equity multiple hurdles. Users can switch between hurdle types via a dropdown menu in cell D4 of the ‘Partnership Returns – Annual’ tab.
- Responding to frequent user requests for monthly waterfall modeling, particularly relevant for shorter-term, opportunistic real estate investments, a dedicated equity waterfall model with monthly periods was created to avoid overcomplicating the primary model with too many options.
- This equity waterfall model, with minor adaptations, has been integrated into numerous standalone models on income-partners.net, including the Real Estate Portfolio Acquisition Model and the All-in-One Model for real estate development and acquisition underwriting.
- Mid-2019 (v1.3 and later) saw the introduction of a GP IRR or EMx Catch Up option, applicable when the LP receives a disproportionately larger share of cash flows in the first tier.
- December 2019 marked an update to the video walkthrough to reflect model changes up to that date.
- In October 2020, based on feedback from Accelerator members in the forums, the model was enhanced to simultaneously test for both internal rate of return and equity multiple hurdles within the same analysis. This significant redesign was accompanied by a video walkthrough explaining the changes.
- April 2021 saw the release of a Monthly version of this model.
- March 2024 marked the release of v1.94, a major update incorporating a fifth tier and the option to select the number of tiers for analysis.
Version History
v1.951
- Logic in C13 revised to ensure LP achieves the greater of IRR or Emx scenario when IRR + EMx is selected.
v1.95
- Resolved issue where the IRR + EMx option incorrectly selected the greater of the two options instead of the lesser; C13 updated to select the lesser of IRR and EMx.
- Fixed link on the Version tab.
- Updated placeholder values.
v1.94 – Mar 19, 2024
- Version tab updated.
- Various placeholder values updated.
- Extensive formula improvements:
- Hundreds of dynamic (IF(Row=””,””,)) hold period logic formulas cleaned up.
- Consistency fixes applied to 16 Ending Balance formulas to include distribution in time zero (no impact on returns as distributions never occur in time zero).
- Link removed from ‘Partnership Returns – Monthly’ to ‘Partnership Returns – Annual’ to facilitate easier tab exporting.
- IFERROR() function added to D40 (Partnership Returns – Monthly) for scenarios where GP fees exceed GP contribution requirement.
- 5th tier built into each of the four waterfalls (IRR Annual, IRR Monthly, EMx Annual, EMx Monthly):
- Tier 5 Input added in row 18.
- Tier 5 section added to each of the four waterfalls.
- Various formulas in Tier 3 and Tier 4 updated to accommodate the 5th tier.
- ‘# of Tiers’ feature implemented, allowing users to select between 2 and 5 tiers:
- ‘# of Tiers’ dropdown input added in row 5.
- Tier assumptions logic updated based on ‘# of Tiers’ input.
- Tiers logic in each of the four waterfalls updated to set hurdles on unused tiers to unattainable levels, effectively disabling them.
- Miscellaneous formatting fixes and updates.
v1.93 – Jan 4, 2022
- Issue resolved where IRR calculation in the Monthly module (rows 68, 76, 85, 160, 168, 177) incorrectly used the annual IRR() function instead of the monthly XIRR() function.
- Error Check formulas in the Monthly module updated to remove unnecessary references to IRR.
- OFFSET() function added to specific XIRR() functions in the Monthly module to ensure dynamic hold period functionality.
v1.92 – Sep 7, 2021
- Issue resolved where GP ‘Required Return’ line on ‘Partnership Returns – Monthly’ incorrectly assumed annual periods instead of monthly.
v1.91 – Aug 2, 2021
- GP Catch Up heading (cell E4) restored after accidental removal.
v1.9 – Jul 7, 2021
- ‘Analysis Period’ output added to cells D19:E19 of both ‘Partnership Returns – Annual’ and ‘Partnership Returns – Monthly’ tabs.
- All XIRR formulas on the ‘Partnership Returns – Monthly’ tab rewritten to include the OFFSET() function for dynamic hold period output.
- IRR hurdle required return calculation updated to use ACTUAL/365 instead of 30/360.
v1.8 – Apr 23, 2021
- Error corrected in the summary of preferred return and return of capital cash flows.
- ‘Partnership Returns – Monthly’ module added:
- ‘Partnership Returns – Annual’ tab duplicated and renamed ‘Monthly’.
- Headers updated to ‘Month’ instead of ‘Year’.
- Required Return formulas updated to assume monthly periods.
- IRR return calculations updated to use XIRR.
- Miscellaneous formatting updates.
v1.7 – Feb 3, 2021
- Breakdown of Preferred Return, Return of Capital, Promote, and Excess Cash Flow implemented:
- Preferred return totals and period breakdowns in rows 22 and 32.
- Return of capital totals and period breakdowns in rows 23 and 33.
- Promote totals and period breakdowns in row 34.
- Excess Cash Flow totals and period breakdowns in rows 24 and 35.
- Miscellaneous formatting updates.
v1.6 – Oct 8, 2020
- Complete model redesign to accommodate IRR + Equity Multiple hurdle (greater of IRR or EMx):
- Dropdown menu in cell D4 now includes ‘IRR’, ‘Equity Multiple’, and ‘IRR + Emx’; selecting ‘IRR + Emx’ ensures LP receives the higher of IRR or EMx results.
- Original waterfall duplicated twice.
- Original waterfall removed.
- One duplicate set to always calculate waterfall based on IRR.
- Second duplicate set to always calculate waterfall based on equity multiple.
- ‘Hurdle Rate Method Used:” calculation added in cell D12; when Promote Hurdle Method (D4) is set to ‘IRR + EMx”, D12 calculates whether IRR or Equity Multiple is higher.
- ‘Summary of Partnership-Level Returns’ section revised to pull cash flow from the appropriate waterfall based on the value in cell D12.
- Conditional Formatting rules revised to accommodate model redesign.
- Extensive formatting updates.
v1.51 – Dec 30, 2019
- Minor changes to dummy values in the template file.
- Various label changes for improved consistency.
- Ability to set 0% GP contribution % added.
v1.5 – Dec 8, 2019
- Walkthrough video updated to include all changes since initial release.
- Mini-tutorial videos added for modeling GP Catch Up and GP Fees.
- New and revised instruction notes implemented.
- Waterfall calculation section set to hide/unhide with a single click; set to hidden by default.
- Print range updated, and instruction notes set to not print.
- Various labels renamed for consistency (e.g., ‘hurdle’ renamed ‘tier’).
- Number format changed to ‘Accounting’.
- Header row revised.
- ‘Sponsor’ changed to ‘GP’ for consistency.
- Miscellaneous formatting updates.
v1.4 – Sept 14, 2019
- Versions tab updated to the new format.
- Year header added above each tier.
- Header design updated to a more modern style.
- Blue background removed from input cells to align with formatting best practices.
- Tier 1 renamed “Return of Capital & Hurdle 1 (Preferred Return” for improved clarity.
- Miscellaneous formatting enhancements.
v1.3 – May 31, 2019
- GP IRR and Equity Multiple Catch Up option added:
- Dropdown menu implemented to enable/disable GP Catch Up.
- Catch Up option appears when GP’s ownership share is >0% but its distribution % in tier 1 is less than its pro rata ownership share %.
- Issue resolved where IRR check label was not functioning correctly when using Equity Multiple hurdles.
- Issue resolved where Equity Multiple number formatting was incorrectly returning a percentage instead of 0.00X.
- VLOOKUP() functions replaced with non-volatile formulas.
v1.22 – December 10, 2018
- Promote Structure inputs and notes clarified to improve understanding of GP promote structure.
v1.21 – July 13, 2018
- Logic added to make hold period adjustments more intuitive; deleting Net Levered Cash Flow from years outside the analysis period now automatically removes those years from the analysis.
- Circular reference in asset management fee input row fixed.
v1.20 – June 17, 2018
- Option to model sponsor fees added; up to two different sponsor fees can be included (e.g., AUM fee, acquisition/disposition fee).
- Reference to USD removed to better support non-US users.
- Tab renamed to ‘Partnership Returns – Annual’ to better reflect module purpose.
- Various formatting improvements.
- ‘Error Check’ formula revised to account for rounding errors.
- Hurdle 1 modeling simplified; sponsor capital account tracking removed.
- Version tracking improved.
v1.10 – December 29, 2017
- Option to use Equity Multiple hurdles instead of IRR hurdles added (cell D4).
- Miscellaneous bug fixes.
- Link to waterfall module with monthly periods added.
About the Author: Spencer Burton, originally from the Northwest United States, brings over 20 years of experience in residential and commercial real estate. Throughout his career, he has underwritten over $30 billion in commercial real estate transactions at leading institutional firms globally. He currently serves as President and a founding team member 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
