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

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 mail box here:

If you want to see the returns of an individual, there’s a handy sheet to do just that.

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

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 share 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 527 FD total of the S-E. The same thing in the S-F. This implies that KKR did the whole of the two rounds.

For the range of values you can graphically see the returns by class of shares. KKR has the E and F shares.They stack up to their total ownership.

Next we have a table for the Dollar returns and you can see KKR at $650m is returning $54m from the S-F and $20m from the S-E.

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

The final table shows the total return profile by class. KKR is taking the E and F classes.

You literally just need to change the name in the drop down menu for the entire sheet to dynamically change.

Click here to continue

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