CAP TABLE #10: 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.

There are 14 parts:

You can join the course and get these sent straight to your mailbox here:

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.

The first assumptions you need to deal with are:

Vest all unissued options: At the exit, you may still have unissued shares from your option pool. You need to decide what happens to these shares. One option is you leave them as is and they go to someone (you decide), 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? Then 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.

This section is an FYI on the date the founder closed and what liquidation preference each class of shares have.

Here are the calculations for acceleration. We mentioned there are three options you can choose from. Here are all the calculations. There are a series of additions and deductions that happen to make the model balance.

Next, we have a summary of your cap table. You can see the shares they own, the fully diluted percentage. You can also see the share price and the investment they made (useful since they have a liquidation preference).

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 the S-D is double the investment.

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 chart on the other hand is the $ allocation to investors. It gets larger over time as the pie is larger. you can progressively see that new classes of shares start getting some of the pie over time as there are more colors.

Now we are getting into the calculations.

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 show the value of the preference for investors. You can see that the first four columns, the S-I investor takes everything
The residual value sections 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 to 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: