Returns waterfall calculation sheet of the cap table

This is the 10th part of the Pro Cap Table training course on the returns waterfall sheet. In this series we go through the basics you need to know, then worksheet by sheet so you know how to make a seriously kick-ass cap table.

Overview of the Returns Waterfall

In the previous episode, we went through how to use the Individual Return Sheet. Well, this episode will be similar, although there are a million more calculations going on!

So why does this sheet matter? Well, who actually cares about the cap table, really? You’re just tracking ownership for something worth less than zero trill you IPO or someone buys you for a tonne of dough, right?

So to move the cap table from a dull-ass admin task to something you care about, I added a cap table version with returns analysis.

This Returns Waterfall sheet in the Pro Cap Table lets you see how much each class of share will make.

This was a massive pain in my a$$ to make for you…

How to use the Returns Waterfall sheet

Let’s get into the big returns waterfall sheet. This maps out by class of share who owns what for a range of exit values.

The point of a cap table is to know who owns what… this is who gets what 😉 A returns waterfall shows how the cash flows down from investor to investor to the founders.

Range of values you may ask? Well, there are lots of potential outcomes so I show you 40 (which you can set) by repeating my calculations 40 times across columns.

Returns waterfall assumptions

There are 546 rows in the waterfall sheet for my cap table. This sheet might look like doing a crossword puzzle where every answer is ‘error 404: formula not found’ but I did all the hard work. you literally can only edit three assumptions in the whole sheet!

I’ll explain the three assumptions now:

• Vest all unissued options: At the exit, you may still have unissued shares from your option pool (Staff haven’t fully vested). You need to decide what happens to these shares. Normally the answer is FALSE.
  • TRUE: You leave them as is and they go to someone (you decide, but ideally to the founders),
  • FALSE: the other is that you delete them and then everyone on the cap table increases their stake proportionally. Set the menu to FALSE to delete them from the cap table
• Do options accelerate: When you have shares fully vested, there is no reason to accelerate shares. When all shares are not fully vested then the remaining unvested shares have the option to fully vest (depending on single and double trigger clause). This button lets you override your acceleration inputs for each staff. There are three assumptions:
  • 1/ Model assumptions (what you specifically input),
  • 2/ Force acceleration (100% vesting) and,
  • 3/ Force no acceleration (No acceleration for anyone)
• Exit date: When does the acquisition happen? The main impact of this is for share vesting.

The last number here is the outstanding fully diluted shares. That’s the denominator for ownership calculations.

Returns waterfall top calculations

After the assumptions, there are four blocks of supporting calculations I had to do. I could hide these at the end of the sheet but they are there so you can check.

We will cover these briefly.

Liquidation preferences

This section shows when rounds closed, and what liquidation preference each class of shares secured. In a returns waterfall, liquidation preferences can really impact who gets what, in particular, the common shares if you raised a lot and didn’t sell commensurately.

Acceleration of Options Assumption

Here are the calculations for the acceleration of options.

There are three options you can choose from, so I show all the calculations. You choose which option you want in the assumptions and I pull the math from here.

In the final two columns, there are two error checks. Given there are a series of additions and deductions that happen to make the model balance I show you the remainders (See: Fractionalisation of shares).

Summary of Cap Table (E.g. Post Series-C)

Next, we have a summary of your cap table with a few key terms.

This includes:

• Fully-diluted shares (both in # and $)
• The share prices participated in
• The $ investment amount

The share price and the investment amount are key when investors have a liquidation preference.

FD Assumptions for Waterfall Calculations – [Unissued Options Fully Allocated]

This is the same table as above but it has the option to include (or not) the unvested shares we mentioned before.

We can also see the actual value of the liquidation preference. You can see that the S-D is double the investment ($10 vs 20m).

Returns waterfall charts

After the assumptions and primer calculations, we have two charts for you to ponder. These are outcomes to review.

Shareholder Returns (% or Total)

This chart shows you the % return for each exit value. It all adds up to 100%.

You can see the S-I investor takes everything for the first 6 rounds and then the S-H starts getting a share.

It’s worth noting that over time the ratios all stabilise to a consistent ratio (This is due to liquidation preferences).

Shareholder Returns ($)

This chart on the other hand is the $ allocation to investors.

It gets larger over time as the pie is larger given your larger exit amounts. You can progressively see that new classes of shares start getting some of the pie over time given more colors.

Returns waterfall calculations

Now we are getting into the calculations.

Assumptions

There are two assumptions you can input to make the range of values work for you.

Whilst I could do some fancy stuff to automate the range min/max, I’ve allowed you to manually set them to your needs.

• Central value: There are a lot of columns which show the range of exit values. To set the central value, input the number. You can make this larger or smaller as fits your potential exit range
• Increments: To increase the steps between columns you input the number. The central value is 1.25 billion. 25 steps of negative 50m gets you to zero. Then you add to the central value and you range up to $2.6bn (We can all dream!)

Here is an example for the Series-I.

• You can see the exit value range from zero to $150m
• There’s a note to show it’s a 1x liq pref
• You see the price per share, the liq pref value per share, the shares they have and the liq pref $ value
• The outstanding shares is a rolling calculation in the model. When the S-I converts to common, you deduct the shares from the outstanding at the S-H round, and this is then used to calculate the remaining value (down the waterfall)
• The convert to common is calculated further down the sheet, this signals whether the investors take their liquidation preference, or convert to common
• The proceed on preference shows the value of the preference for investors. You can see for the first four columns, the S-I investor takes everything
• The residual value section shows what is left for the next stage of investors. You can see no value for the next series as the preference has taken it up.
• The actual return section shows what investors get (and if that is below the preference)
• The tests to convert to common are just proofs to show you what is required for the preference to convert to common. I have set out three requirements for this to happen. Only when all three light up like Christmas trees is there a conversion. You can easily check what is happening on each row

As we move further up the exit range, you can see the magic lights for common conversion happening.

The first column shows you that two requirements are met but not the third.

It’s only in the second column that all three are met and there is a conversion.

After all the S-I to S-A calculations, we get to the summary calculations.

You can see the total preferences and the remaining proceeds. The checks show that everything adds up.

The residual common value is the total amount for all common holders after preference investors have taken their cut.

Here we can see the common shares of each class of investor. If you see the blue, empty boxes, that’s because the preference holders are taking their preference.

The % of common proceeds is just the % of the common value that each investor class takes. This will add up to 100% when the exit is large enough.

You can see the returns waterfall below:

Here you can see the dollar value of the returns. This will line up exactly with the above.

This is the same as the common but it shows the returns for liquidation preferences. This is the inverse of the common.

Here are the total $ returns to each class of investor in total.

This is the per share dollar returns waterfall and the multiple on their investment.

Pretty awesome, huh!

If you don’t have the model yet, get it here.

There we go. Let’s get moving to the next episode.

Read next step

14 parts in this guide

You can jump to a section if you prefer:

1. What is a cap table and other important questions
2. Cap table dilution step-by-step example
3. Cap table dilution math
4. Starting the cap table (The drop-down menus we need)
5. Shareholders sheet
6. Deal calculations
7. The cap table sheet
8. The assumptions sheet
9. Individual shareholder returns sheet
10. Returns waterfall calculation
11. The ESOP sheet
12. The Common sheet
13. The convertible notes and warrants sheet
14. The preference shares sheets (From Series A to I)