Purpose
The purpose of this post is to explain the key features of the Waterfall Assumptions which is found standard in the majority of CREModels Excel-based underwriting templates. This guide will explain how to set up the involved parties and distribution of cash flow, as well as a few ways to modify the reports.
Equity Contribution Breakdown
The Equity Contribution Breakdown section allows the user to designate the parties involved in the waterfall. There are three groups involved in the waterfall which can be labeled by the user. The Investor Group is usually the main investing party that is putting in the majority of the equity. The GP’s Contribution is the GP Co-invest (if applicable), and the GP’s Promote is the Promote/Manager. This section also allows the user to control whether or not there is a co-invest and in what amount. For example, if it’s a 90/10 contribution the user would set the Investor Groups Percentage Contribution to 90%, in which case the GP’s Contribution will automatically become 10%. If there is not a co-invest, the user just needs to set the Investor Group Percentage Contribution to 100%.
Equity Structure
Once the parties have been designated, the next step is to build out the Equity Structure. First the user will have to select the number of tiers they wish to use (up to 4). Note that Pref is considered Tier 1, so selecting 4 tiers means using Pref plus 3 additional hurdles. Select 1 Tier to use only Pref.
a) Preferred Return and Return of Capital
When distributing cash flow, the model will first service Pref before paying back invested capital. Pref can be viewed as interest on a loan and in most cases, the longer it takes to pay it back the more Pref accrues. There are four different options for calculating Pref and they are as follows:
- Non-Compounding – Also known as simple interest, this will simply calculate the total Pref accrual based on a % input. Time is not a factor for Non-Compounding so longer periods to return capital will not accrue more Pref.
- Compounding – If the user selects Compounding, they will notice that they can also choose the compound frequency. The frequencies can be Annual, Semi-Annual, Quarterly, or Monthly. Unlike Non-Compounding, the longer it takes to return capital the more Pref will accrue, therefore any setbacks in distributing cash flow can create additional costs.
- IRR – The IRR selection will calculate similarly to Compounding Monthly, but will be slightly different. This is because all CREModels use the XIRR function in Excel which uses a monthly compound, but looks at the actual number of days in the month when determining how much Pref to accrue. The Compounding Monthly option does not take the number of days into consideration.
- Multiple – Like Non-Compounding, time is not a factor when using Multiple. Multiple is simply used to calculate a fixed amount of Pref based on the amount invested. For example, if the invested equity is $20,000,000 and the Multiple is set to 1.25, then the Pref to be paid will be $5,000,000.
b) Hurdles
After Pref has been paid and capital returned, the model will then progress through the remaining hurdles distributing cash flow on a Pari Passu basis. The first thing the model will do is begin distributing cash flow to the Manager based on the entered split. In the example below, 20% of the cash flow will go to the Manager while 80% will go to the Limited Partners (LP)
Following the above example, the cash flow distributions will work as follows:
- Pref and return of capital will be paid to the Investor Group and GP’s Contribution (the combined being the LP) up to a 6% IRR;
- Thereafter 80% will go to the LP and 20% to the Manager until an 8% IRR;
- Thereafter 70% will go to the LP and 30% to the Manager until a 12% IRR;
- Thereafter 60% will go to the LP and 40% to the Manager until a 15% IRR;
- Thereafter 50% will go to the LP and 50% to the Manager.
Because the GP is also contributing equity, the GP’s total distribution will actually be a bit higher than just the Manager Split %. For example, After the 6% IRR Pref has been paid the GP will receive their 20% as the Manager, and they will receive 10% of the 80% that is going to the LP to effectively receive 28% of the total cash flow (20% for Manager plus 10% of 80% equaling 8%), while the Investor Group is receiving 72% of the total cash flow (90% of the 80%).
The above inputs give the following cash distributions when using a Co-Invest:
Without a Co-Invest (no GP contribution), the cash distribution will look like this:
The above reports are from the Equity Structure and Returns tab.
Each hurdle can have a different return type than the prior. For example, the user can have Pref be based on IRR, but then have all hurdles thereafter set to Compounding. Also note that the Multiple option is only available on the Pref level and is not available for any subsequent hurdles.
c) Equity Payment on Capital Events Only
The user has the option to choose whether or not to pay capital back when cash becomes available or only during capital events. Capital events in most models are usually when any loans begin (after Acquisition or Construction loans), or during the sale of the property. The option is available in the Equity Structure section below Number of Tiers.
If the option is set to Y, then only cash flow during months of capital events will go towards paying back capital, while other months will either just pay Pref or begin distributing through the following hurdles. If the option is set to N, then the model will follow the distribution schedule above where it will pay Pref, return capital, and then continue through the hurdles using all available cash flow. Please note that this option is not available when using the IRR Pref type due to the nature of the IRR calculation.
d) Up-Front vs. Overtime (Development Models Only)
In addition to the above, the user also has the option to choose whether equity is contributed Up-Front or Over Time. This is only applicable to development models as the equity will be spent based on the timing of the development schedule, while acquisition models will always require equity to be Up-Front. If the Over Time option is selected, the equity will be contributed on a monthly schedule based on what the actual equity needs are each month during the development period. If Up-Front is selected, all equity will be contributed at the beginning of the Analysis and will act as a reserve account for equity to be drawn from as needed.
Fund Fees to GP
Fund Fees to GP are fees that are taken out of Levered Cash Flow before being distributed to the above parties, the net result is Levered Cash Flow Net Fees which is what will ultimately be distributed. There are many different fees and they vary between the different model types, though some of the fees are consistent across all models such as the Asset Management Fee and the Disposition Fee. The below image is from our Multi-Family Development model.
While the Fees do ultimately go to the GP, they will not be reflected in the GP’s returns on the Equity Structure and Returns tab.
Implied Contributions
Implied contributions are used to apply any non-monetary contributions to the waterfall. A common example of this would be a land contribution in which the owner of the land will contribute it to the deal in order to gain a stake within the deal as opposed to outright selling it. The user simply needs to enter the dollar amount of the contribution and give it a label. The implied contributions effect on the model is that it will dilute the % stake of the Investor Group and GP’s Contribution. The user also has the option to select whether or not the Implied Contributions are from the GP. If Y is selected then the value of the Implied Contribution will be added to the GP’s stake.
Reporting
a) Investor Breakdown
The Investor Breakdown section allows the user to divide the Investor Group into up to 5 different entities solely for reporting purposes. To do this, the user just needs to enter up to 5 labels and the % that each group consists of the total equity contribution by the Investor Group. The user can also enter an Initial Investment which can be used as a baseline metric for the same report.
The report can be found at the bottom of the Equity Structures and Return tab where the blue bar is a dropdown to select which investor to display, or the Initial Investment report. Changing the dropdown will show what they investors investment amount is, and what their returns look like based on such investment. This will give investors a personalized tear sheet to view when evaluating the deal.
b) Cash on Cash Calc
The Cash-on-Cash calc options allow the users to select what numbers the yearly Cash-on-Cash should be based on. The first option allows the user to select whether to use the Original Equity, or Outstanding Equity for the denominator. The second option allows the user to select whether to include or exclude capital events.
The Cash-on-Cash returns can be found on the Equity Structure and Returns tab as pictured below.
