Thursday, February 14, 2008

Waterfall Hurdles



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.

Tuesday, February 5, 2008

Excel tip: remove gaps in lists

Here's a formula to remove gaps in a range so that the list is contiguous:

{=IF(ROW()-ROW(W$53:W$69)+1>ROWS(A$53:A$69)-
COUNTBLANK(A$53:A$69),"",INDIRECT(ADDRESS(SMALL(
(IF(V$53:V$69<>0,ROW(A$53:A$69),ROW()+ROWS(A$53:A$69))),
ROW()-ROW(W$53:W$69)+1),COLUMN(A$53:A$69),4)))}

W$53:W$69 is the destination range where the gapless list items will be placed. This formula would be copied to each cell within this defined range. Notice it is a collection (ie. hit ctrl-shift-return when accepting the cell formula).

A$53:A$69 is the source range of values you want to copy. In this example, I want to copy the data in this column if the test of column V on the same row is true. The value in column A of the row where V is true is copied to the same row in column W, or a zero value is put in column W of the same row if the test is false.

V$53:V$69 is the test range. If this test is true, then it's a cell that has value.

If you want to use a different source range to determine whether to include a row or not, change the bolded range (IF(V$53:V$69)<>0) to the condition you want to check. It has to have the same number of cells as the source and destination.

Notice also the absolute rows. If your data is in a row with multiple columns, set the column to absolute. This way, you can copy the formula to every cell within the range easily, since the same formula is used in each cell within the destination range.