waterfall financial model example
This will be needed when calculating the LP distribution in this tier and also our ending balance. Lets take a look at how this works. Profits are split pari passuup until the LP earns a 10% IRR. You can toggle between IRR hurdles and Equity Multiple hurdles using the drop-down menu in cell D4 of the Partnership Returns Annual tab. This is accomplished by taking the LPs distributions for the same period in this tier, dividing by the LPs percentage share of cash flow in this tier, and then multiplying by the GPs share of cash flow in this period. Suppose we have a general partner and an outside investor who contribute a combined total of $1,000,000 into a project. The waterfall structure can be thought of as a series of pools that fill up with cash flow and then once full, spill over all excess cash flow into additional pools. You will only need to provide your email address the first time. When calculating the cash flow splits we are also taking into account any prior distributions made in Tier 1. 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, Revised Error Check formula to account for rounding errors, Simplied hurdle 1 modeling; no longer tracking sponsor capital account, Added option to use Equity Multiple hurdles, rather than IRR hurdles (cell D4), Added link to waterfall module with monthly periods. Just like in Tier 2, all of the cash flow in years 1 through 4 is distributed in the prior tiers, which is why all the cash flows in Tier 3 are from the sale in Year 5. I believe the easiest way to grasp this concept is to focus on the acquisition and sale of one business. The sponsor cash flow begins with the GP Contributions line item. Real Estate Equity Waterfall Model IRR and Equity Multiple Hurdles . The return hurdle is simply the rate return that must be achieved before moving on to the next hurdle. Second, 100% of all cash inflows to the LP until the LP has received a preferred return on the capital invested in step 1. The LP Distribution line item takes the lesser of 1) the Beginning Balance, plus LP Contributions, plus the LP Required Return line item, minus LP Prior Distributions taken, or 2) the projects cash flow before tax. Usually the amount of the promote is clearly defined as a percentage. The table above has a lot of information, so as we work through it below remember that all we are doing is calculating what a 10% IRR to the LP looks like. Lets take a closer look at how these percentage splits are calculated. In this case the GP Distribution % would be 20% + (10% x 80%), which equals 28%. If the pref is cumulative then it will be added to the investment balance for the next period and accumulate until its eventually paid out. As the number of private equity groups and fundless sponsors has grown, I have seen a variety of new approaches, this being one of them. This field is for validation purposes and should be left unchanged. Third, thereafter, cash flows in excess of distributions made in step 1 and step 2 (if any) are distributed 80% to the LP and 20% to the GP. Finally, well calculate how much remaining cash is available from the project that can flow into the next waterfall tier. Then, once we figure out what cash flows are needed for a 10% IRR to the LP, we calculate the amount of cash flow that gets distributed to the LP and the GP. This is simply the amount that is owed to the LP based on the beginning balance and the 10% IRR requirement for this hurdle. Typically the sponsor prefers the lookback provision (since they get to utilize money even if they have to eventually give it back), while the investor prefers the catch up provision (since they get paid first and wont have to ask the sponsor to make them whole at the end of the deal). Finally we have our LP IRR check, which shows the total cash flows made to the LP, including cash flow from the prior tier. Year 0 is the beginning of the project and as you can see our LP beginning balance is $0. A screenshot of this tab has been provided below. Another common component in equity waterfall models is the preferred return.
If you have any questions about our Pay What Youre Able program or why we offer our models on this basis, please reach out to eitherMikeorSpencer. After the 10% IRR is achieved by the LP, then profits will be split disproportionately. When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. 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. You can find the latest version of the model, the updated walkthrough video, and a changelog for the model below. A link to this file will be sent to the following email address: If you would like to send this to a different email address, Please click here then click on the link again. In fact, several of the models in our library of real estate Excel models have this exact real estate equity waterfall module built-in. Paid contributors to the model receive a new download link via email each time the modelis updated.
Models downloaded from A.CRE may contain errors. We also get your email address to automatically create an account for you in our website. The first hurdle is straightforward. Then we calculate an IRR on these cash flows to ensure the LP is in fact earning a 15% IRR to satisfy the second return hurdle. If youd 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. Although waterfall structures vary widely, there are several commonly used waterfall model components. The hurdles can either be internal rate of return (IRR) or equity multiple hurdles. This line simply starts with the property level cash flow for each period and then accounts for the distributions taken in tier 1 by the LP and GP. The model will recognize those periods as unnecessary and hide them from the analysis. Using the same example as above, the GP Distribution % would be calculated as 10% + (20% x 90%), which equals 28%. Upon a successful exit (sale of the business), the GP would earn a carried interest (share of the proceeds) for their efforts. As a result, we continue to update and improve the model as we find time and based on the feedback we receive. Real estate distribution waterfalls often follow a similar approach, but the split between the GP and LP is more favorable to the GP. That leaves the investor with 63% of the cash flow. Finally, we walked through a detailed 3 Tier waterfall model example step-by-step. formatting best practices), Renamed Tier 1 to Return of Capital & Hurdle 1 (Preferred Return for better clarity, 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 GPs ownership share is >0% but its distribution % in tier 1 is less than its 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, Cleaned up Promote Structure inputs and notes to make it more clear how Sponsor is being promoted, Added logic to make adjusting hold period more intuitive; simply delete Net, Fixed circular reference in asset management fee input row, Added option to model sponsor fees; can include up to two different sponsor fees (e.g. If the distributions are LP 1st in the 1st tier, the model offers the option for the GP to catch up to the LP. Though simplified, this is perhaps the most frequently cited example for a private equity transaction. The catch up provision provides that the investor gets 100% of all profit distributions until a predetermined rate of return has been achieved. Each waterfall is on its own separate worksheet. Is the preferred return compounded? You can see my response and a further explanation of the methodology we use by reading this blog post.
The third tab titled 20% After Pref & 80_20 adds one step to the sequence above so that the 20% catch up is limited to distributions made after the return of invested capital. When the pref is cumulative a key question is, is this unpaid cash flow, What is the compounding period? 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, Fixed issue where GP Required Return line on Partnership Returns Monthly assumed annual, rather than monthly, periods, Fixed issue where GP Catch Up heading had been removed (cell E4), 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, 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, Return of capital total and by period in rows 23 and 33, Excess Cash Flow total and by period in rows 24 and 35, Complete redesign of model to accommodate IRR + Equity Multiple hurdle (i.e. We continue this process for all years in the holding period and once completed we can then move on to calculating cash flows for the Sponsor in this tier. The promote defines the disproportionate share of cash flow the sponsor or general partner will receive for achieving return hurdles. Now we need a way to actually calculate the profit splits at each tier. The model is built on an annual and monthly basis for up to 10 years of analysis. Also, with a little more effort, the model can be adjusted to use both equity multiple hurdles and IRR hurdles together. Then the sponsor will receive its pro rata share of whats left over after the promote is paid by the partnership. Since all of the equity for this project is required at the beginning, it is all shown at time period 0. Fill out the quick form below and we'll email you our real estate waterfall Excel model containing helpful calculations from this article. Over his career, he has underwritten $30 billion of commercial real estate at some of the largest institutional real estate firms in the world. Equity waterfall models in commercial real estate projects are one of the most difficult concepts to understand in all of real estate finance. Once the return hurdle has been defined the next logical question is, from what perspective will the return be measured? However, the contributions are treated as a negative value here since it is a cash outflow and is required to be shown this way for the IRR calculation. For example, suppose the GP owns 10% of the partnership and the LP owns 90%. The Ending Balance line item takes the sum of the LP Beginning Balance, LP Required Return, LP Contributions, and then subtracts LP Distributions. I also wanted to include it to demonstrate the degree to which there is flexibility in negotiating the terms of a distribution waterfall. In this process, the limited partners would provide the capital to make the acquisition, and the general partner would identify the target company, work towards an acquisition, manage the investment over a three to seven year period, and finally identify a buyer. partnership-level module). This allows the user to forecast when certain hurdles are hit, and what share of the total distributions goes to which distribution type. All rights reserved. In this table we are simply adding up the cash flows from each tier for both the investor and the sponsor. This type of arrangement is beneficial because it allows equity investors to reward the operating partner with an extra, disproportionate share of returns.
Then we simply repeat the process discussed above by calculating our LP Required Return based on the beginning balance for this period, then we calculate any new LP Contributions, LP Distributions for this period, and finally our Ending Balance and IRR check for this period. Since the release of this feature, a user asked why the preferred return distributions occur first followed by the return of capital distributions. Cash flow from a development or investment project can be split in a countless number of ways, which is part of the reason why real estate waterfall models can be so confusing.
All equity investors (which includes both the general partner and the third party investor) receive pro rata distributions until the LP achieves a 10% annual preferred return on their invested capital. Then, after the investor achieves the required return, 100% of profits will go to the sponsor until the sponsor is caught up.. The sponsors share of cash flow would be calculated as follows: GP Distribution % = Promote % + GP Pro Rata Share x (1 Promote %). After the 10% preferred return hurdle has been achieved, all additional cash flow will be distributed 90% to all equity investors in accordance with their ownership percentages, and 10% to the sponsor as a promote, until the LP has achieved a 15% annual return. As a result, it assumes you have already modeled the property-level cash flow in your own DCF. the greater of IRR or EMx), Drop-down menu in cell D4 now includes IRR, Equity Multiple, and IRR +, 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, 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, Minor changes to dummy values in template file, Various additional changes to labels to add greater consistency, Added ability to have 0% GP contribution %, 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. In this case it is 30% + (10% * 70%), or 37%. In October 2020, in response to several requests from. Are you an Accelerator member? If the pref is compounded then its also important to know the. Third, a 20% catch-up to the GP equivalent to 20% of the distributions realized in step 2 plus the distributions realized in this step. The video uses v1.5 of the model and incorporates all of the enhancements to-date. The next few lines show how much equity is contributed to the project by the sponsor and investor and when it is contributed. In December 2019, I updated the walkthrough video for this model. First lets take a look at our project level cash flow before tax and equity contributions over the holding period: The first line is simply our before tax cash flow calculation from a standard real estate proforma. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); In this article we tackled the real estate equity waterfall model, which is perhaps the most complicated topic in real estate financial modeling. The model allows for up to four tiers (IRR or equity multiple hurdles). LP contributions are $0 in Year 1. The key difference is that this time we are calculating the cash flows required for the LP to earn a 15% IRR and then we are splitting them up between the investor and the sponsor at different rates. In the Excel file attached you will find four simple examples in increasing order of complexity. For instance, its not uncommon to see a structure described as something like 90/10 to an 8%, then 80/20 to a 12%, then 50/50 thereafter. Often these arent promote amounts but rather the final equity splits that should occur after the promote is taken into account. Add that logic to each of your cells, and the result is a model fully dynamic to hold period. This articlepublished in the Real Estate Finance Journal takes a deeper dive into the nuances of the promote calculation. Next, we calculate the GP distributions using a shortcut method. This LP Distribution calculation takes the lesser of 1) the Beginning Balance, plus LP Contributions, plus the LP Required Return line item, or 2) the projects cash flow before tax. With investment waterfalls, cash flows are distributed according to the owners agreement.
In this case the sponsor gets a 30% promote plus 10% of the remaining 70%, which equals a total GP share of 37%. 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. A few key questions with the preferred return are: The lookback provision provides that the sponsor and investor look back at the end of the deal and if the investor doesnt achieve a predetermined rate of return, then the sponsor will be required to give up a portion of its already distributed profits in order to provide the investor with the predetermined return. The reason why we subtract out prior distributions is because we just want to distribute the cash needed to earn between a 10% return up to a 15% return. Finally, profits above a 15% IRR will be split 63% to the investor and 37% to the sponsor. Next we have the GP IRR check, which is calculated in the same way the LP IRR check is calculated. This is simply taking what we start with (beginning balance), then adding in the required return for this period and any new equity contributions, then accounting for whats been paid out (the distribution). A Simple Model exists to make the skill set required to build financial models more accessible. The final tab gets a little more creative, and rewards the management team and sponsor according to IRR hurdles achieved. It is also common to refer to the sponsor as the general partner (GP) and the investor as the limited partner (LP). We have a 90%/10% equity split between the third-party investor and the sponsor, and then we have a 3 tier promote structure. Notice that both of these calculations lead to the same result. This is what actually gets distributed to the LP in this tier. Second, a 20% catch-up to the GP equivalent to 20% of the of the distributions realized in step 1 plus the distributions realized in this step. The catch up provision is essentially a variation on the lookback provision and seeks to achieve the same goal. What exactly is the preferred return? So far all of our assumptions are pretty straightforward and easy to understand. The remaining cash to distribute calculations are also identical to the prior tier, except we use the cash available to distribute after hurdle 1 has been met rather than the cash flow before tax from the project itself. This is calculated in the same way as the prior tier, except we now also take into account the LP Prior Distributions as well. After the 15% return hurdle has been achieved, all additional cash flow will be distributed 70% to all equity investors in accordance with their ownership percentages, and 30% to the sponsor as a promote. Further suppose that the GP will earn a 20% promote after a 12% preferred return hurdle is achieved. Now lets take a look at the second IRR hurdle and repeat the same process we followed for Tier 1: This table is exactly like the table used above for the first hurdle. This is the same calculation as hurdle 1 with one key difference. If distributions in any year fall below the preference level of 10%, then the deficiency will be carried over to the following years and compounded annually at the preferred rate of return. Notes: As of version 1.8, the model now includes two waterfalls: an Annual waterfall and a Monthly waterfall. Recently, Ive also been working to update the walkthrough videos that accompany each of my models. Preferred investors could include all equity investors or only select equity investors. For example, the promote could be defined as 10% after the investor earns an 8% preferred return. This is important to clearly define because the return hurdles (or tiers) are what trigger the disproportionate profit splits. In private equity transactions this generally focuses on the relationship between the general partner (GP) and limited partners (LP). Since our beginning balance is $0, the required return and distributions are also both $0. This new line item tracks the distributions allocated to the LP in the prior tier for the first hurdle. In Year 1 we use the ending balance from the prior year (Year 0) as our Year 1 Beginning Balance. 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. As you can see, the calculated IRR for the entire project is 21.24%. Fourth, thereafter, cash flows in excess of distributions made in step 1, 2 and 3 (if any) are distributed 80% to the LP and 20% to the GP. Finally, lets take a look at the last hurdle, which is an IRR above 15%: This is the easiest to calculate since we dont have to figure out the required cash flow for a particular IRR. An investment waterfallis a method of splitting profits among partners in a transaction that allows for profits to follow an uneven distribution. We regularly update the model (see version notes). This means there is unfortunately no one size fits all solution and the only way to understand a specific waterfall structure is to read the agreement. This waterfall model assumes only one sponsor (i.e. The agreement will spell out in detail how profits will be split among partners.
He is currently Head of Real Estate Investments and member of the founding team at Stablewood Properties. While there are some commonly used terms and components in investment waterfall structures, waterfall structures can and do vary widely. What is a Waterfall Model in Real Estate? Here is a summary including percentage allocations of the total equity contributions to the project: As you can see, the sponsor provides 10% of the equity, or $100,000, and the third-party investor contributes 90% of the equity, or $900,000. Note: Excel file available for download at the bottom of this post. We discussed some common components in equity waterfall models and emphasized the importance of reading the owners agreement in order to truly understand a waterfall structure. This shortcut method allows us to quickly calculate the distributions for the GP without going through the entire process like we did for the LP. The LP Contribution line item is next and this is the amount of equity contributed by the LP in this period. We use cookies to ensure that we give you the best experience on our website. This equity waterfall model is built for inclusion in other models or in other words it is not standalone.
