What Is a Cap Table and How to Build One in a Spreadsheet

What is a cap table?

If you're an entrepreneur who's raising money for your startup, then you'll need to create a cap table. A capitalization table is a document that shows the ownership percentages and dilution of each investor in a company. It can be daunting to create a cap table from scratch but don't worry; we've got you covered! In this article, we'll give you a quick intro to what cap tables are and how to build them in spreadsheets.

What does a cap table look like?

A cap table typically shows the following information:

• Names of shareholders
• Number of total shares and per shareholder
• Percentage share per shareholder
• Share price and valuation

Some cap tables contain additional information, such as the type of shares, or show the development over time.

Now that we know which data you would find in a cap table, let's have a look at how to build one.

How to build a cap table?

We will be using this cap table as a reference. It is not only a reflection of the capitalization at a single point in time, but it shows how the cap table developed from incorporation to a first and a second financing round.

Let's check out how the cap table builds up over time.

Let's assume there are two founders who incorporate a company with a total share capital of €10,000. Accordingly, the initial cap table should look as follows:

Note: We made two assumptions here:

1. a) Both founders hold the same number of shares.
2. b) The nominal value of a share is €1.

This was easy. Now, let's have a look at the first financing round. Here, things become a little more interesting because valuations come into play.

The scenario we assume is as follows:

The founders succeed in convincing the first two investors/business angels for their startup, who each want to invest € 250,000 on a pre-money valuation of €2 million.

Now, what is a pre-money valuation? The pre-money valuation of a company is its valuation before the investment. Accordingly, the post-money valuation of a company is its valuation after an investment.

Pre-money valuation + Investment = Post-money valuation

In a financing round, the investors acquire new shares. These shares have to be issued. Let's have a look at the math behind that.

The number of shares that need to be issued in a financing round is a function of the investment, the valuation, and the current number of shares.

In the above example, the new investors invest a total of €500,000 on a pre-money valuation of €2 million. Accordingly, the post-money valuation is €2.5 million.

This information is not quite enough to calculate the number of new shares, but it allows us to calculate the percentage share of the new investors.

New Investment / Post-money Valuation = Percentage Share of New Investors

Accordingly, the new investors will hold 20% of the company after the investment (€500,000 / €2,500,000).

We do know the current number of shares, which makes calculating the number of newly issued shares easy.

If the investors will hold 20% of the company after the investment, then the number of shares they acquire divided by the total number of shares post-investment needs to equal 20%.

Shares of New Investors / Total Shares post-investment = Percentage Share of New Investors

Because we know the number of current shares (total shares at incorporation), we can easily calculate the number of shares that need to be issued for the new investors.

We know that:

Total Shares pre-investment + Shares of New Investors = Total Shares post-investment

Accordingly:

Shares of New Investors / (Total Shares pre-investment + Shares of New Investors) = Percentage Share of New Investors

Now, let's solve for the number of new shares:

Shares of New Investors = Percentage Share of New Investors * Total Shares pre-investment + Percentage Share of New Investors * Total Shares pre-investment

Then:

Shares of New Investors (1 - Percentage Share of New Investors) = Percentage Share of New Investors * Total Shares pre-investment

And:

Shares of New Investors = (Percentage Share of New Investors * Total Shares pre-investment) / (1 - Percentage Share of New Investors)

Let's fill the variables with our example data:

Shares of New Investors = (20% * 10,000) / (1 - 20%) = 2,500

Accordingly, the number of Total Shares post-investment is 12,500.

If we assume that the new investors in the example invest equal amounts, the resulting cap table will look as follows:

Note: We could have also solved for the number of shares post-investment and then calculated the number of new shares by subtracting the number of shares pre-investment. That's what we did in the spreadsheet.

Please see our example cap table for more details and one additional financing round. You can reconcile the calculations by looking at the individual cells.

The modeling of subsequent financing rounds, of course, follows the exact same logic.

One topic we haven't covered until this point is dilution.

What is dilution?

Dilution is the reduction of a shareholder's percentage share when the total number of shares increases.

Dilution = (1 - Percentage Share post-investment / Percentage Share pre-investment)

In the above example, let's have a look at the founders' dilution resulting from the first financing round.

Before the investment, the founders each held 5,000 shares and, accordingly, 50% of the company. With the new financing round, their absolute number of shares did not change, but new shares were issued, resulting in an increased number of total shares.

Post-investments, the two founders each hold 40% of the company (5,000 / 12,500).

Accordingly, their dilution is 20% (1 - 40%/50%).