How to Build a Cap Table in Excel

A cap table tracks who owns what in a company: founders, investors, and the option pool, share by share. In Excel it becomes a living ledger that shows ownership before and after each financing round, the dilution that follows, and the fully diluted percentages that drive valuation negotiations. Building it cleanly keeps the percentages honest as rounds stack up.

What a cap table does

A capitalization table lists every security a company has issued: common shares, preferred shares from each round, options, warrants, and convertible notes. For each holder it records the share count and the resulting ownership percentage.

It fits a model by translating dollars raised and a price per share into new shares, then recalculating everyone's slice. Pre-money valuation plus new investment equals post-money, and the new investor's ownership is investment / post-money. The cap table is where that arithmetic is enforced holder by holder rather than as a single blended number.

Build it step by step

Lay out holders down the rows and rounds across the columns. Each round adds a share count and recomputes the percentage column so you can read ownership at any point in time.

Start with founder common shares, add the option pool as a reserved block, then layer each priced round on top.

1. Enter founder and existing share counts as hardcoded inputs in the first column.
2. Add the option pool as its own row, sized as a target percent of post-round fully diluted shares.
3. For a new round, compute price per share as =pre_money / pre_round_fully_diluted_shares.
4. New investor shares are =investment / price_per_share; add that as a new row.
5. Recompute each holder's percentage as =holder_shares / total_fully_diluted_shares.

=2500000/12000000 gives the Series A holder 20.8 percent of the 12,000,000 fully diluted shares.

The formulas that tie it together

Total fully diluted shares is the sum of every issued and reserved security: =SUM(common, preferred, options, warrants). Every percentage divides a holder's count by this denominator, so the percentages always total 100 percent.

Dilution from a round is pre_round_% - post_round_% for an existing holder. Because their share count does not change but the denominator grows, the percentage falls. A founder at 80 percent on 8,000,000 of 10,000,000 shares drops to =8000000/12000000, or 66.7 percent, once 2,000,000 new shares are issued.

• Price per share: =pre_money / pre_round_fully_diluted_shares.
• New shares issued: =investment / price_per_share.
• Post-money: =pre_money + investment, which should equal price_per_share * post_round_shares.
• Ownership check: the percentage column must SUM to exactly 100 percent.

Pitfalls and what reviewers check

The most common error is sizing the option pool after the round instead of before. Investors usually require the pool to be set in the pre-money share count, which means the pool dilutes founders, not the new investor. Check whether the pool sits above or below the price-per-share denominator.

Reviewers confirm that share counts are clean inputs rather than formulas pointing at other cells, that percentages sum to 100 percent in every column, and that post-money equals price per share times post-round shares. Hidden hardcodes inside formula cells are the usual culprit when a column refuses to total correctly.