Financial modelling8 min read
Cap Table: Modelling Dilution Across Multiple Rounds
A cap table, or capitalisation table, records who owns what in a company, including the rights to future shares, and what each funding round changes. This guide shows how to build one in Excel across several rounds, with a worked example, the option pool, convertible instruments and the split of the exit price.

What a Cap Table Is, and What It Must Show
A cap table (short for capitalisation table) lists a company's shareholders, the number of shares each holds, the rights that will turn into shares later, and each holder's percentage. A company that has never raised money has a very simple one: two founders, 100,000 shares, 60% and 40%. From the first funding round it gets more complicated: preferred shares for the investor, a BSPCE (French founder share warrants, the local equivalent of employee stock options) plan for employees, sometimes a BSA-AIR (the French equivalent of a SAFE) that will convert at the next round. The cap table then becomes a model in its own right, and it has to answer three questions: who owns what today, who will own what after the next round, and how much each holder receives on a sale.
Two columns of percentages sit side by side, and you need to tell them apart from the first row. The undiluted percentage divides each holder's issued shares by all issued shares. The fully diluted percentage adds to the denominator everything that can become a share: options, BSPCE, free shares, warrants and convertible bonds. A round is negotiated on this second basis: when an investor asks for 20% of the company, they mean fully diluted.
The Columns of a Well-Built Cap Table
One row per holder, one column per instrument, one block per round. The table below is the starting point for Hexalog, the fictional software company from the article on pre-money valuation, before its first round.
| Holder | Ordinary shares | Preferred shares | BSPCE | Fully diluted | % fully diluted |
|---|---|---|---|---|---|
| Founder 1 | 60,000 | 0 | 0 | 60,000 | 60.0% |
| Founder 2 | 40,000 | 0 | 0 | 40,000 | 40.0% |
| Total | 100,000 | 0 | 0 | 100,000 | 100.0% |
Each round adds a price-per-share column and two checks: the percentages total 100%, and new shares multiplied by the price give back the amount raised. Keep the date and price of each issue outside the table too. The cap table serves as supporting evidence during due diligence, and a buyer will want to trace every row back to a shareholder resolution.
Modelling a Round: From Amount Raised to Number of Shares
A round is described by three figures: the pre-money valuation, the amount raised and, almost always, the size of the option pool the investor requires before coming in. The rest is calculation.
Price per share = pre-money ÷ fully diluted shares before the round.
New shares = amount raised ÷ price per share.
Hexalog raises €2m at an €8m pre-money valuation. With no option pool, the price would be 8,000,000 ÷ 100,000 = €80, the investor would subscribe for 25,000 shares and hold 25,000 ÷ 125,000 = 20%. But the term sheet says "BSPCE pool of 10% post-money, included in the pre-money". The BSPCE to be created therefore enter the denominator before the round, and because their number depends on the post-round total, the calculation looks circular. It is not: think in shares of the company rather than in numbers of shares.
After the round, the investor holds 20% and the pool 10%, so the founders keep 70% with their 100,000 shares. Post-money, the total is 100,000 ÷ 0.70 = 142,857 shares, of which 28,571 go to the investor and 14,286 to the pool. The price per share is 2,000,000 ÷ 28,571 = €70, which equals (8,000,000 − 1,000,000) ÷ 100,000: the founders' effective pre-money is €7m, because the pool absorbed the missing million. As a formula, for an investor targeting stake i and a pool of stake p:
Post-money shares = existing shares ÷ (1 − i − p).
Hexalog's Cap Table Across Three Rounds
Eighteen months later, Hexalog raises €6m in a Series A at an €18m pre-money valuation. There are two versions of the round, depending on whether the new investor accepts the pool as it stands (7.5% after dilution) or requires an option pool top-up to 10% post-money, still included in the pre-money.
| Holder | Incorporation | Seed round | Series A, pool unchanged | Series A, pool topped up to 10% |
|---|---|---|---|---|
| Founders | 100,000 (100%) | 100,000 (70.0%) | 100,000 (52.5%) | 100,000 (50.6%) |
| Seed fund | 0 | 28,571 (20.0%) | 28,571 (15.0%) | 28,571 (14.4%) |
| Series A fund | 0 | 0 | 47,619 (25.0%) | 49,451 (25.0%) |
| BSPCE pool | 0 | 14,286 (10.0%) | 14,286 (7.5%) | 19,780 (10.0%) |
| Total fully diluted | 100,000 | 142,857 | 190,476 | 197,802 |
| Price per share | €1 (nominal) | €70 | €126 | €121 |
Without a top-up, the price is 18,000,000 ÷ 142,857 = €126 and the fund receives 6,000,000 ÷ 126 = 47,619 shares, or 25% of 190,476. With a top-up, the founders and the seed fund share 65% of the post-money capital: total 128,571 ÷ 0.65 = 197,802 shares, 49,451 for the Series A, 5,494 more BSPCE, and a price brought down to €121. The pool top-up costs the founders 1.9 points and the seed fund 0.6 points; at a €24m post-money valuation, those 2.5 points are worth €0.6m. The cap table should produce this figure before the negotiation, while it can still be used as an argument.
The seed fund drops from 20% to 15%, but its 28,571 shares are worth €126 each, or €3.6m for €2m invested: dilution costs it nothing as long as the price rises. Each investor's IRR calculation can be read straight from the cap table, using their entry price and the price of the next round.
Instruments That Are Not Yet Shares
Three types of rights must appear in the fully diluted column, with a conversion assumption written down in black and white.
- BSPCE, options and free shares. Count the whole plan, granted or not: unallocated securities will be granted, and a buyer will reason the same way. In the undiluted percentage, count only securities that have been exercised.
- BSA-AIR and convertible bonds. The investor put in money before the round, and their price will be the round price, with a discount (most often 20%) and a valuation cap. They convert at the lower of the two prices. Suppose Hexalog had issued a €0.5m BSA-AIR before the Series A, with a 20% discount and a €12m cap: discounted price 126 × 0.8 = €100.80, capped price 12,000,000 ÷ 142,857 = €84. The holder converts at €84, receives 500,000 ÷ 84 = 5,952 shares, against 3,968 at the round price. Model the conversion at the cap price until the round is signed: it is the most dilutive assumption, and a Series A investor will insist that their 25% stake be calculated after this conversion.
- Ratchet clauses. If the next round is priced lower, the earlier investor receives free shares. A full ratchet brings their entry price down to the new price; a weighted average only corrects it in proportion to the shares issued at the discount. These extra shares come out of the founders' stake: provide a row to receive them, even if it is empty.
What the Cap Table Does Not Tell You: Splitting the Exit Price
A fully diluted percentage does not translate directly into euros on a sale. Preferred shares most often carry a liquidation preference: the investor takes back their investment before any split, or their share of the price if that is worth more. Take Hexalog after the Series A with no top-up, sold for €20m.
| Holder | Fully diluted stake | Pro rata | With a 1x non-participating preference for the Series A |
|---|---|---|---|
| Series A fund | 25.0% | €5.0m | €6.0m (its investment, higher than €5.0m) |
| Founders | 52.5% | €10.5m | €9.8m |
| Seed fund | 15.0% | €3.0m | €2.8m |
| BSPCE pool | 7.5% | €1.5m | €1.4m |
The Series A prefers its €6m to 25% of €20m; the remaining €14m is shared among the others pro rata to their shares, that is 70%, 20% and 10% of the capital excluding the Series A. The seed fund converts: €2.8m is worth more than its €2m investment. The founders lose €0.7m compared with pro rata, and more if the preference is participating or multiple. This waterfall is modelled on a separate sheet, which reads the cap table and applies the clauses of the shareholders' agreement in order: preferences, participation, then pro rata. The clauses themselves are covered in the article on pre-money valuation.
Building the Cap Table in Excel
- An assumptions sheet. For each round: the pre-money valuation, the amount, the pool's stake and its rule (inside the pre-money or after), and the discount and cap on convertibles. None of these figures is entered anywhere else.
- One block per round, in columns, reading the previous block: shares before, new shares, shares after, price, percentage. The stake formula (existing shares ÷ (1 − i − p)) avoids iterative calculation; never enable circular references for a pool.
- Checks under each block: percentages sum to 100%, new shares × price equal the amount raised, the investor's stake equals the amount divided by the post-money valuation.
- The exit waterfall on its own sheet, with the sale price as an input and a sensitivity table that varies that price from €10m to €50m: it shows the price above which each preference stops biting.
- A next-round scenario, even a hypothetical one. The cap table is most useful for answering "what if we raised €10m at €30m in two years?" before an investor asks the question.
Common Mistakes
- Counting the unallocated pool in the undiluted figures, or leaving it out of the fully diluted ones: either way, the price per share is wrong.
- Calculating the pool after the round when the term sheet puts it before. The gap is one point of capital for each point of pool, taken from the founders.
- Ignoring convertibles until they convert. A BSA-AIR with a low cap can be worth more shares than the round itself.
- Confusing percentage with sale proceeds. Only the waterfall tells you what a stake is worth.
- Keeping the cap table in one file and the share transfer register in another, with no reconciliation: at the first round of due diligence, the gap is paid for in days of delay.
Link the cap table to the value of the company. The free IRR model calculates each investor's multiple and return from their entry price and exit price; the company valuation, free on screen with a free account, gives you the exit price to test. These are estimates, not a certified valuation or investment advice: have your lawyer and accountant review the cap table and the clauses of the shareholders' agreement.


