How to Make a Simple Cap Table

After searching for hours on Google - I couldn’t find anything that was easy to understand, up to date, or very dynamic. In a recent interview, I learned how to make a very easy-to-understand cap table that shows ownership, payout, and can all be changed by adjusting the exit price. This is just a simple cap table that won’t cover any complex terms, however, this is a great starting point for those just getting started.

I know that I valued this during and after the interview process, so I hope you find it useful as well.

If you are unfamiliar with cap tables, here is a great article from TechCrunch that goes into pre-money and post-money valuation, the purpose of cap tables, as well as the dynamics of a couple of early rounds of funding.

Part One

Fill in the columns, from left to right, with the number of shares (everyone), share price (only for investors), the cost (multiply previous two columns together), liquidation preference (equal to the cost), and finally the ownership percentage (number of shares divided by total number of outstanding shares). For this example, I will use a cap table that I previously made on BitGo, a leader in institutional-grade financial services for digital assets. The share prices and amount of shares issued per round are complete assumptions and were made for the process of learning.

You should now have something that looks like this:

I grouped the rows of investors in each round, so the image didn't take up half the page.

Part Two (a)

To get started on the second part of this cap table, we need a few pieces of information to continue.

1. A hardcoded exit value/price (when we’ve finished making the entire model, this is the only cell you will have to edit to conduct a payout analysis on different valuations)
2. You’ll need to have a way to reference when each round receives their preference and when they convert to common stock. In this case, lets say each new round of funding has preference over the previous. So, the Series B-2 round has preference over Series B-1 round and so on.
3. Next, there needs to be a way to show the value at which each round converts to common stock.Let’s call these our “Breakpoints” — I’ll explain how to calculate this with the following picture:

Now that we have this information, we can continue.

Part Two (b)

Next, use this information to show whether or not a round needs to be converted to common stock (using an if statement to check if the exit value is larger than the break-point of that round), how much that common stock is worth, and then the ownership of common stock.

Finally, use this information to create another table that shows the breakdown and value of the total proceeds to each of the same shareholders as in the last image.

Since the company was last valued at just under $170M (again — an arbitrary valuation for the purpose of learning), each of the preferred shareholders’ shares has converted to common stock and have the proceeds outlined above.

Part Three

From an investor’s perspective, it is important to understand what the potential payout could look like. Rather than looking through the previous table, make a final one breaking down each of the investors from each round of fundraising, the value of their proceeds, and their ownership in the company. The easiest way to do this is to nest a vlookup() function within a sumif() function to find the cumulative value of proceeds and ownership for the specified investor.

Hopefully, this helped you in making a very simple, yet dynamic, cap table. All you should have to do is change the exit value in order to see the effects on each of the shareholders. This is something that I learned recently and found really valuable, and hope that you find it valuable too. If you have any questions, corrections, or suggestions, please feel free to let me know!