Pro cap table training course

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:

  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)

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:

Here you can see the dollar value of the returns. This will line up exactly with 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!

Click here to continue

The ESOP sheet

Show off to your friends. Hit a social button to share the cap table love!

Get in the game

Free tools and resources like this shipped to you as they happen.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.