A cap table that calculates itself

Ask a founder two rounds in for their cap table and you usually get a spreadsheet where the percentages were entered by hand. Somebody worked them out once, at the round, from a number an investor's analyst produced, and they have been copied forward since.

It works until a question gets asked. What would this round look like at a ₹35 crore pre-money instead of ₹40 crore? What does the pool going from 10% to 12% cost us? What happens if we raise ₹12 crore rather than ₹10 crore? None of those can be answered by a table of percentages, because the percentages are the output and nothing in the file knows how they were produced.

This template holds the arithmetic instead. The inputs are the three things a term sheet actually states, and everything else follows from them.

Rounds

The engine. One column per round, with pre-money valuation, new money and the ESOP pool target as the only inputs. It returns the price per share, shares issued to the investor, the pool top-up, total shares and the existing holders' share afterwards. A tie row recalculates the pre-money from the price and tells you whether it matches what you entered.

Cap table

The fully diluted table after the last round, pulling share counts from Rounds, with amounts invested and the price each holder paid. Four checks at the bottom confirm it reconciles.

ESOP grants

What has been granted out of the pool, with vesting calculated against today's date, so it updates itself every time the file opens. Cliff and vesting period are per grant.

Dilution

Ownership after each round, side by side, with a founders combined row across the bottom. This is the sheet that actually answers the question founders are asking.

Read me

What each sheet does, the one convention worth understanding, and what the file deliberately does not try to do.

The file arrives with an invented history: two founders, a seed round and a Series A. Every figure is made up, and the arithmetic is exact.

Read the last row across. That is dilution: 100%, then 70%, then 52.89%, and the founders have not yet sold control. The two rounds together raised ₹12 crore and cost them 47 points of ownership, of which the ESOP pool accounts for a meaningful share.

The final table splits out as 31.73% and 21.16% for the two founders, 15.11% for the seed investor, 20.00% for the Series A investor, and 12.00% in the pool. The seed investor's 20% at the round has become 15.11% two years later without them doing anything wrong, which is what happens to every investor who does not follow on.

The example uses CCPS for both rounds, because that is what Indian priced rounds actually use. Compulsorily convertible preference shares convert to equity on agreed terms and are where liquidation preference and anti-dilution provisions live.

For ownership arithmetic, CCPS behave like equity on a fully diluted basis, which is why the table treats them that way. Where they differ is at an exit, and that difference is not about percentages at all: it is about who gets paid first and how much. A cap table cannot tell you that, and it is worth being clear that this file does not try.

• ·Convertible instruments before a priced round, usually CCDs in India, convert at a cap or a discount and produce a share count this file cannot derive on its own. The convertible note calculator works that out, and the resulting shares can then be added as a holder row.
• ·Several notes on different caps convert at different prices, which needs a row each rather than a single column.
• ·Anti-dilution adjustments issue extra shares to earlier investors in a down round, and the size depends on the formula in your documents.
• ·Multiple tranches within one round are separate events at separate prices.

A cap table that is subtly wrong is worse than not having one, because decisions get made from it. So the file checks itself in five places.

• ·Each round recalculates the pre-money valuation from the price per share and the share count, and compares it with what you entered. If that row does not say Ties, an input is wrong.
• ·The founder rows must add up to the shares issued at founding.
• ·The cap table total must match the total from the last round.
• ·The percentage column must add to 100%.
• ·Options granted must not exceed what the pool holds, which is a mistake that happens more often than founders expect once grants are being made every quarter.

Look at those before reading anything else. They take a second and they are the difference between a file you can hand to an investor and one you have to apologise for.

The most valuable ten minutes with this file happen before the round, not after it.

1. 01Enter the terms as offered, and look at the founders combined row.
2. 02Change the pool target to what you think you actually need from the hiring plan, rather than the number in the term sheet, and see the difference.
3. 03Try the pre-money 15% lower, because term sheets move and the arithmetic should not be a surprise if it does.
4. 04Try raising less. Founders rarely model this, and a smaller round at the same valuation sometimes reaches the same milestone with several points less dilution.
5. 05Then add the next round as a fourth column, at a valuation you would be pleased with, and look at the founders row again. Two rounds ahead is where the compounding becomes visible.

None of this changes what an investor will agree to. It changes which line you spend the negotiation on, and that is usually worth more than the negotiating itself.

• ·No liquidation preference, so it says nothing about exit proceeds. Ownership and payout are different questions and the liquidation waterfall calculator covers the second.
• ·No anti-dilution mechanics, because the adjustment depends on the formula in your specific documents.
• ·No convertible conversion, which needs the cap and discount worked out first.
• ·No secondary transfers, buybacks or share splits. All three are ordinary events and all three need a row-level change rather than a formula.
• ·It is not your share register, and it does not produce anything you can file.
• ·One class of investor per round, converting compulsorily to equity, which covers most Indian rounds and not all of them.

It answers one question properly: given these terms, who owns what, and what did each round cost. For a company two or three rounds in, having that in a file that recalculates is worth considerably more than a tidier one that does not.