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.