Individual shareholder returns sheet of the cap table

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

Overview of the returns sheet

You don’t make a capitalisation table for fun. The goal is to start a company, work your booty off and then sell out. So the question is who makes what?

In the cap table version with the returns analysis I start out doing the math for all classes of shareholders. I’m sure you’d like to be able to see what each individual or firm makes, so I did that for you.

In this episode we will look into the returns for an individual.

Using the individual return sheet

Tl;dr: There is nothing that you need to do in this sheet other than to pick the individual you want to see the returns for. You pick the individual in the drop-down and boom.

Pick the shareholder

There is only one input you need and that is to pick the name of the shareholder from the drop-down menu. In the example, you can see that KKR is an investor. So here we are will see how much they make (or lose).

You click the name from the drop-down to pick KKR.

The exit date is pulled from the Returns Waterfall sheet. You can pick any date in the future. The main benefit of this is that you can see the effect of vesting.

The table is a list of each class of shares and then the total fully diluted shares by class.

The final column shows you how much of the total FD shares they own. You can see that KKR holds 527k of the 527k fully diluted total of the Series-E.

The same thing in the Series-F. This implies that KKR did the entirety of both rounds and no other investors participated.

Chart of returns for the individual shareholder

I have made a chart which shows a range of values for the returns of KKR. This means that I did 40 different calculations and you can see how much KKR makes from the bottom to the top of the range.

Whilst the calculations are done below, you can graphically see the returns by class of shares here.

Note that there are two colors (yellow and blue) for the shares KKR has in the E and F rounds. They stack up to their total return amount.

Calculations for the individual returns in the cap table

Realistically the only interesting parts for you are at the top which we just went through, but let’s finish what is in the rest of the sheet.

There are three blocks of formula calculations with conditional formatting. These show:

1. Returns to the individual shareholder in the cap table
2. The % the investor held of each class of share
3. The $ returns (or other currency if you used the 50Folds Excel plugin to change the currency to one of your choosing) to the shareholder

Returns to the individual shareholder in the cap table

The first calculation block we have is a table for the Dollar returns to the shareholder.

You can notice in the top left that the name is shown automatically. I’ve pimped out the model to be as smart as possible for you.

There are 40 columns to show a range of values of outcomes for the investor.

We can see if your company has an exit for $650m, KKR is returning $54m from the S-F and $20m from the S-E.

You’ll notice that KKR make nothing if you sell for $600m.

Have a look and see what they make at a $900m exit.

Holding of Class for Shareholder (%)

This table shows the % of the class of share that KKR owns. You can see in both cases they own 100% of the class.

If you had selected yourself as a founder you would see that you would appear in the common row.

Returns to Investors ($)

The final table shows how much each shareholder makes in terms of wonga.

In order to make the chart we take how much the shareholder has, multiply by the returns by class of share and get the outcome.

KKR is taking all of the E and F classes of shares, so 100% of the E will equate to their return.

Conclusion

This is an outcome sheet to understand how much money you, staff, and investors will make.

Once you know how to use the model, you can learn how to run scenarios and see how things will work out before you exit.

You literally just need to change the name in the drop-down menu for the entire sheet to dynamically change and to see results which is pretty awesome!

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)