Don’t forget to share this post

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.

What Is Budgeting and Why Is It Important for a Business
What Is Budgeting and Why Is It Important for a Business?

Discover what budgeting is, the goals and questions your budget needs to answer, and why creating a budget is essential for any business.

READ MORE

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:
What Is a Cap Table and How to Build One in a Spreadsheet Initial Cap Table
What Is a Cap Table and How to Build One in a Spreadsheet - Initial Cap Table

Note: We made two assumptions here: a) Both founders hold the same number of shares, and 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
What Is a Cap Table and How to Build One in a Spreadsheet Percentage Share of New Investors
What Is a Cap Table and How to Build One in a Spreadsheet - 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

How to Prepare for Budgeting Season?

Finance leaders at StackOverflow, Ironclad, Landing, and more, share their insights

DOWNLOAD FOR FREE
How to Prepare for Budgeting Season Guide

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:

What Is a Cap Table and How to Build One in a Spreadsheet Shares of New Investors
What Is a Cap Table and How to Build One in a Spreadsheet - Shares of New Investors

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%).

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Sep 15 2022, Updated Sep 23 2022