Real Estate Equity Waterfall Model with Catch Up and Clawback (Updated Dec 2024)

A question came up recently in the A.CRE Accelerator’s real estate waterfall modeling course regarding how to model GP Catch Up (i.e. Sponsor Catch Up) and LP Clawback provisions. I put together a quick video tutorial in response to that question, and in doing so I realized I had never shared to A.CRE a real estate equity waterfall model with the ability to model a Sponsor profit catch up or LP clawback. So allow me to introduce the ‘Real Estate Equity Waterfall Model with Catch Up and Clawback’.

In April 2020, this model was updated to include the ability to include sponsor fees. The three sponsor fee types added are acquisition/development fees, asset management fees, and disposition fees. The fees are assumed to be paid by the partnership and distributed to the sponsor, after which any remaining cash flow is distributed to the partners per the waterfall assumptions.

And in May 2021, a monthly version of the model was created. This monthly version is included as a second worksheet/tab in the model. The monthly version works identical to the annual version, only cash flows are assumed to occur on a monthly basis.

Note: If you’re an A.CRE Accelerator Advanced member, you’ll learn how to build this model in the Advanced Modeling – Partnership Cash Flow endorsement. Not yet an Accelerator member? Consider joining today.

What Types of Partnership Structures This Model Can Handle

This equity waterfall module is one of the more versatile that I’ve built for the A.CRE Library of Excel Models. While no equity waterfall module can do it all, this particular module covers many of the common structures you might run into – both in the United States and internationally.

In terms of what this tool can do. With this module, you can

• Model either American-style or European-style waterfalls (read the definition of American vs European waterfalls).
• Use either hard or soft hurdles (read about soft hurdles)
• Model soft hurdles using Sponsor Profit Catch Up and/or LP Clawback provisions
• Assume the Sponsor contributes some or no capital
• Set custom distribution percentages for each partner; by default distribution percentages are determined by a promote assumption, which is assumed to be paid by the partnership (read about promote calculations)
• Use separate distribution assumptions during operation compared to at reversion; this feature is available in my Equity Waterfall Model with a Cash-on-Cash hurdle as well
• Model up to two tiers during operation and up to four tiers and three IRR hurdles at reversion
• Add in sponsor fees and calculate the sponsor cash flow and returns
• Analyze a hold with up to 10 annual periods (plus time zero)

Given the versatility of this model, I plan to add it to our A.CRE All-in-One model in the near future.

How to Use the Real Estate Equity Waterfall Model with Catch Up and Clawback

I’ve built this module to be visually similar to my other real estate equity models. So if you’ve used them, this will feel familiar. The general logic of the model works as follows:

1. During operations. Distributions are made to the partners based on their pro rata share of contributions until the LP has hit its preferred return. Next, excess cash flow goes to payback accrued pref but NOT to pay down the original capital account balance. Thereafter, any excess cash flow is split per the distribution percentages in cells G11:H11.
2. At the capital event. Distributions are made to the partners based on their pro rata share of contributions until any accrued preferred return has been paid and each partner has received a full return of capital. Thereafter, excess cash flow is distributed based on the three IRR hurdles and promote assumptions entered in cells E14:H17.
3. European-Style Waterfall. To set the model to be European-style (i.e. no promote paid until LP receives full return of capital and hits preferred return), simply set the Sponsor’s promote during operation to be 0%. Otherwise, set a promote for the Sponsor during operation and the Sponsor will be promoted before the LP receives a full return of capital. Note that European-style waterfalls are actually far more common these days, even in the U.S., and the European-style waterfall is the methodology our other models use.

Allow me to now discuss each of the inputs in this module, and then I’ll finish with a quick video walk-through of the tool.

1. Ownership share. The percentage you enter determines the contribution percentage by the partners. The Catch Up and Clawback provisions are only available when the LP contributes 100% of the required capital.
2. Sponsor Profit Catch Up Option. Set the LP ownership share to 100% and toggle ‘Sponsor Catch Up’ to ‘Yes’ for this module to be activated. Once activated, the logic is as follows:
   • The LP receives 100% of distributions during operation until all capital has been returned and it has achieved an internal rate of return over the hold period equal to the preferred return assumption.
   • Then, 100% of all excess cash flow is distributed to the Sponsor (i.e. the catch up) until the Sponsor has received a share of the excess profits (i.e. Total LP Distributions – Capital Contributed) equal to its first promote level (cell F15).
   • So if the LP was distributed 500,000 in profit in order to hit its preferred return and the Sponsor promote level is 15%, the Sponsor will be distributed in its catch up the lesser of any cash flow remaining or 500,000/(85%) x 15% = 88,235 ; which equates to 15% of the total profit required to get the LP to its preferred return.
   • Important Note: This model uses a ‘profit catch up’ feature, not an IRR catch up. Meaning the Sponsor catches up to the percentage of profits distributed, rather than to a given IRR hurdle. This is necessary for structures where the Sponsor does not contribute capital to the deal. If you need an IRR catch up, refer to my standard Equity Waterfall Model.
3. LP Clawback. Set the LP ownership share to 100% and toggle ‘LP Clawback’ to ‘Yes’ for this module to be activated. Once activated, the logic is as follows:
   • Any promote distributed to the Sponsor during operation is subject to the Clawback if the LP does not reach its preferred return hurdle at the capital event.
   • You can test this by setting the Sponsor promote during operation (cell F11) to a value greater than 0 and then setting the ‘Reversion Cash Flows’ to 0. With insufficient property-level cash flow for the LP to achieve its preferred return, the Sponsor will be obligated to return its promote paid during operation (see row 27 – Sponsor Distribution and row 81 – Sponsor Adjusted Distribution)
4. Distribution – Operation. In cells E10 and F111, set the preferred return hurdle rate and the Sponsor promote % above that hurdle. The LP and Sponsor distribution percentages are automatically set based on the ownership share (preferred return) and the Sponsor promote.
5. Distribution – Capital Event. In cells E14:H17 you can model the return of capital and three promote tiers for the capital event cash flow.
6. Property-Level Cash Flows. For this module to work properly, you will need to link the levered property-level investment cash flows (i.e. negative acquisition and development cash flows from your DCF), operating cash flows (i.e. cash flow after financing line), and reversion cash flows (i.e. net proceeds from sale). In the below video I show you how to link this model to my All-in-One model.

Video Walk Through – Real Estate Equity Waterfall Model with Catch Up and Clawback

Allow me to show you how to use this model. If you have questions, please don’t hesitate to reach out.

Download the Real Estate Equity Waterfall Model with Catch Up and Clawback

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.

---

Version Notes

v1.7 – December 12, 2024

• Added ROUND() function to ‘Error Check’ formulas to avoid false error alerts when calculation exceeds 15 decimal places
• Added Asset Management ‘Start’ input (C52/C54); sets the period when Asset Management Fees begin to be charged to the Partnership
• Updated placeholder values
• Updated Version tab

v1.6 – May 1, 2021

• Created a ‘GP Catch Up to % of Profit:’ input (cell L5); GP now catches up to that percentage of profit, rather than the promote in tier 2
• Altered naming convention in B100 to refer to catch up as “Unpaid Profit” rather than “Unpaid Promote” to more accurately reflect structure
• Made heading in cell B99 dynamic to the GP profit catch up percentage input
• Fixed issue where Catch Up error check was visible when Catch Up module was turned off
• Added Analysis Start date input
• Built Monthly edition of the model (see ‘Partnership Returns – Monthly’ tab)

v1.5 – October 14, 2020

• Fixed issue where in edge cases, Distributions turned negative
• Fixed issue where in edge cases, LP Clawback turned negative
• Fixed Conditional Formatting in ‘GP Catch Up Error Check’
• Misc. formatting updates

v1.4 – August 2, 2020

• Fixed issue in ‘Cash Flow Available for Distribution’ line under ‘Capital Event’ section to properly distribute mid-hold capital events
• Fixed issue with GP Unpaid Promote line to accommodate situations where cash flow is available to cover GP catch up mid-hold
• Added GP Catch Up Error Check logic to confirm accuracy of catch up
• Now breaking out Preferred Return, Return of Capital, Excess Cash Flow, and Promote by partner

v1.3 – April 15, 2020

• Removed legacy Named Ranges
• Renamed GP and General Partner to ‘Sponsor’
• Added Sponsor Fee module
  • Input Sponsor fees in row 44
  • Sponsor fees included in Sponsor cash flow and return calculation
  • Updated tier cash flows to reflect sponsor fees
• Misc. formatting enhancements

v1.20 – April 2, 2019

• Fixed an error in the Tier 1 Distribution row where in rare instances, the Tier 1 cash flow exceeded the hurdle
• Fixed an error where GP Distribution % in Tier 3 was incorrect

v1.10 – March 21, 2019

• Added logic to handle rare situations where there is a period with negative Investment CF

v1.00 – March 19, 2019

• Initial release