To calculate the distribution of cashflow using a waterfall structure, the basic sequence is as follows:

Given the following waterfall structure:

Pref: 10% 90/10 split

Hurd 1: 13% 60/40

Hurd 2: 14% 50/50

Hurd 3: 15% 45/55

Over 15%: 40/60

You need to calculate a given period's interest for the equity unpaid for that period:

Pref: 10% * Unpaid Equity + 10% * Unpaid Pref if the pref is compounding

Unpaid Equity is the total equity of all investors.

There's two ways in Excel to do this. Use the following if you have multiple investment of capital initially over several periods.

Row 1: Previous period's unpaid Equity + new Equity contribution for this period

Row 2: 10% * Row 1

Row 3: 10% * Row 5

Row 4: Any distribution to pay down pref (negative value)

Row 5: Unpaid Pref = Sum of Rows 2 through 4

Here's a sample spreadsheet:

Sample Waterfall Spreadsheet (*)

To save a copy of the worksheet, click on the link and near the top of the worksheet, select "File -> Download as".

The other way is as follows. Use the following if you have one initial investment and no additional contributions as each contribution would require its own accumulated interest calculation.

If you know how long an investment has been held:

=(Unpaid_Equity * (1+pref_rate)^length_of_investment) - Unpaid_Equity

The above formula will return the amount of Pref due given the length of investment time passed.

The same formula is used for each of the hurdle rates.

This sequence would carry over multiple columns where each column represents another time period (month, year, etc.).

The above sequence for the Pref calculation is now applied for each of the hurdles.

Once this calculation is laid out, given a cash distribution, you can determine how the amounts are split according to the waterfall.

That is done in the following sequence:

Typical capital deal structure states return of pref first, then return of capital. This is applied only when there is sufficient cash to distribute at a capital event. If there is insufficient funds to pay both pref and capital, the net effect of paying the pref first is that the investor will simply get their return of capital (full or partial).

After the pref and capital to paid down, depending on whether the capital structure is pari passu or not, will determine the amount of remaining dollars to distribute. If the structure is pari passu, then all equity contributors get their pref and capital at the same time from the distribution. If the structure is not pari passu, then the agreement determines who gets paid first, then second, then third, etc. After all equity investors have their pref and capital paid down, the remaining amount of money is profit for distribution according to the hurdles 1 - x, however many x levels there may be in the terms.

The delta between levels (ie. pref and hurd 1, hurd 1 and hurd 2, etc.) is split according to the split terms for that hurdle level. For example, Pref is 10% and Hurd 1 is 13%. From the calculations done above for both these rates, you subtract the amount of Hurd 1 from Pref to determine the amount that is split 60% to primary investor, 40% to secondary investor.

In excel, you would calculate it as follows:

You can either return equity first, or pref. End consequence is the same.

The following would be applied once for the primary investor at the primary investor split rate, and the secondary investor would have the same formula with their split rate.

(Pref payment) Row 1: = max(0, min(pref, distributable_amount)

(Return Capital) Row 2: = max(0, min(unpaid_equity, distributable_amount - row1)

(Hurd 1 Payment) Row 3: = max(0, min(hurd_1 - pref, distributable_amount - row 1 - row 2)

(Hurd 2 Payment) Row 4: = max(0, min(hurd_2 - hurd_1, distributable_amount - row1_to_row3)

(Hurd 3 Payment) Row 5: = max(0, min(hurd_3 - hurd_2, distributable_amount - row1_to_row4)

(Remaining Cash) Row 6: = max(0, distributable_amount - row1_to_row5)

After the above calculation, split each row's amount by the split terms.

*Note: At the time of posting the sample worksheet on Google Doc's, the XIRR function in Google's spreadsheet had a problem dealing with ranges beyond those with values so you can either export to Excel or change the IRR formula to just the range with data. If you export to Excel, you might have to change the XIRR formula's range as well, temporarily, and then change it back.

## 34 comments:

The waterfall concept appears elegant. I understand things better when i see them operating in a spreadsheet. Can you post an xls example?

Pat

It's pretty complicated and yet not complicated. The reason why it's complicated is because it's compounding. That means you have to keep track of unpaid amounts and compound that amount as well as the principal.

If I have time, I'll post an example worksheet. in the next few days.

great...i'll check back

Sorry Pat, been a busy 4 day week. Will try to put up a workbook next week. I have to make another one for work anyway.

Gents,

Another aspiring Waterfall devotee here trying to get my head around the calculation fundamentals. Would you be so kind as to help me set up a caculation with three tiers and two investors? Scenario is for hypothetical commercial real estate investment deal. I'm still not clear on "hurdle" vs. "target" etc so take it easy on me with new terms! Thanks in advance for any insight you can share with me. Boe

The waterfall assumes two parties where the profits are split based on a "waterfall" structure. The waterfall is really IRR hurdles that determine how profits at each level are split. In a pari-passu deal, all dollars distributed are split at the same ratio as invested up to the preferred return rate. Technically, the preferred return rate is the first hurdle, and all dollars necessary to return the pref yield is split "equally". So if party a puts in 90% and party b puts in the remaining 10%, all dollars are split 90/10 until each receives say a 9% annual yield. The next hurdle determines how dollars above 9% up to the next IRR hurdle is split. So if the next hurdle is 13%, each party gets enough dollars to get to pref. Dollars remaining are split at the next ratio until each party receives that hurdle rate. So if hurdle two is 13% at 70/30, each party splits the remaining profits 70/30 until the primary investor gets 13% IRR. At this point,investor B will receive a higher IRR due to the leverage. They invested 10%, but am now receiving more than investor A for dollars above the 9% pref. This is how you create leverage for investor B. Typically, this is the sponsor group, and the fact that they're no longer pari-passu above the pref means they are being "promoted". They carry a promoted interest in the deal based on whether they can return a yield above the Pref to the primary investor.

If you want an excel model that demonstrates this, just email me.

As far as I know, the waterfall structure is a typical real estate capital structure where the minority investor is typically the operating sponsor. They have a promoted interest in the investment by coinvesting a small amount of the equity to have their skin in the game, but the primary investor sets target yields as hurdles. As I described, the target yield specifies different "hurdles" such that if the operator returns a yield higher than that target rate, they capture more of the profits above that rate, and thus is promoted for performance.

For anyone I've sent an example workbook to, I've fixed an error in how I was calculating the waterfall amounts. The difference is about 50-100 bps IRR loss to LP. The problem was I forgot to take out any amounts paid in the lower IRR hurdles in the higher IRR hurdles, making the cash distributions off (too much given at the lower IRR tiers. If anyone wants the revised version, just email me.

Simon any chance of you posting an XL file as an example. Whilst I really appreciate your detailed explanation in the original post. It is extremely difficult to follow it through in that manner. A live example file would be very much appreiciated

I don't believe this takes into account when a payment of capital will put an investor over the top into the next hurdle,

For example, If the split changed from 90/10 to 80/20 after the 1st partner gets $500k of their initial equity back, and the amount put towards paying back initial equity, after a return on equity or pref, is $600k, then you have $500k split and 90/10 and $100k split at 80/20, how do you account for this?

Serial,

Typically, the waterfall structure will sequence distributions first to repayment of pref, then the repayment of equity, and the remaining balance is split according to the waterfall. The waterfall level only changes when the return to investors meet the hurdles IRRs, of which Pref is the first IRR hurdle.

In your case, if there is $600k to be split, and the total equity was $500k, the $600k would normally be split for the pref first, then the balance to initial equity. Assuming a 10% pref, on $500k, that would mean in 1 year, 50k would be split 90/10, then the initial equity of 500k split 90/10, the remaining 50k would be split at the next waterfall level 80/20 as it would be the cash balance remaining and we've met the 10% hurdle at 90/10.

Hi Simon,

Following your post and comments in the forum but I still cannot grap my understanding the calculation fundamentals. Would you be so kind as to help send me the excel model/ calculation that I guessed you sent to some gents in the forum. Thanks in advance for any insight you can share with me. Buu

My email: lequangbuu@google.com

Simon,

it would be most helpful if you could share your waterfall spreadsheet. Would you mind emailing a copy to me? Thank you.

Simon - this is extremely helpful. Can you pls e-mail me a copy of the spreadsheet?

Could you send me a copy of the cash flow waterfall?

wayne.lewis2@gmail.com

Can you email me a copy of the xsheet also?. It is very helpful.

jonrbenoit@gmail.com

Thank you for posting this. It is very helpful. Could you please send me a copy of the excel file?

Thanks!

trust.robbie@gmail.com

Simon - I would also find a copy of your model helpful.

Thanks,

smithrobs1@gmail.com

Simon - Very helpful insight. Could you also please send me the model. I would like to get a better sense of how the waterfall is modeled in excel. ccouttsws@gmail.com

Thanks

Chris

Simon - Very helpful insight. Could you also please send me the model. I would like to get a better sense of how the waterfall is modeled in excel.

Thanks

Chris

Great insights. can anyone forward a copy of the waterfall in the excel file?

thanks a bunch.

samicalsf@hotmail.com

Simon-thank you for your blog, would it be possible to receive a copy of an waterfall distribution on excel? i would like to understand in in depth how this model is structured. Thanks again for your great insights.

otaola.e@gmail.com

Simon was very clear in walking me through the calculation in this blog. I sure can create one now. But I can save the time if someone can forward me the final Excel file. Appreciate all your help- both Simon and other commentors.

chitidio@gmail.com

I know this thread is almost four years old, but could someone send me the excel file with the waterfall if they still have it?

Thanks,

Joe

josephjct@me.com

Can someone please send me this excel sheet? joeyverrant@gmail.com

Can someone please email me the excel spreadsheet at joeyverrant@gmail.com?

Thanks!

I would also really appreciate the model if someone could send it to me at jaredlevitt@yahoo.com

Thank you very much

I would really appreciate it if you could send me this spreadsheet as well.

brady_larsen@yahoo.com

Simon,

Great post! It is very helpful and clarifies a lot. Is the spreadsheet still floating around? If so, can you please email it to me at oszman1@hotmail.com?

Thanks!

Simon -

Thanks for the explanation! I too understand it better by reviewing a working spreadsheet. Would you (or someone else here) email me a copy of the Excel spreadsheet at: txosu@prodigy.net

Thanks!

Simon - thanks for the explanation. Would you be kind enough to email me the revised model for the waterfall at f_oz@hotmail.com. Thanks a lot.

The link in the post is the only model I have. Just follow the link and you can save a copy of it as an Excel workbook.

Hi Simon, Thank you for this spreadsheet.

I have one question - i have put 0% in preferred return rate. How come at Hurdle 2, i have amounts showing up as "Pref Paid" if i have no preferred returns?

Hi Nar, the column is called "Pref Paid", but it's actually total distribution for the final period (sum of pref paid for previous hurdle + split).

Post a Comment