Building a Cap Table Model in a Spreadsheet
A useful cap table spreadsheet models four things: current fully diluted ownership, the conversion of outstanding instruments, any option pool expansion, and the distribution of proceeds in an exit. The interaction between conversion and pool expansion is what founders get wrong, because each one changes the denominator the other is calculated against.
The four things a model has to show
Current fully diluted ownership. Every share issued, every option granted, every option authorized but unissued, and every outstanding convertible instrument shown as the claim it represents. Founders who track only issued shares consistently overestimate their position, because the pool and the instruments are already claims even though nothing has happened yet.
Conversion. What happens to each outstanding instrument when a priced round occurs. Each instrument converts on its own terms, which is why several signed at different times behave differently in the same event.
Pool expansion. Term sheets frequently require a pool sized as a percentage of the post-closing company. Where that pool comes from changes who pays for it.
Exit waterfall. How proceeds are distributed at a given sale price once liquidation preferences are satisfied. This is the number that answers the question founders actually care about, and it is the one most spreadsheets omit.
A model that shows the first and skips the rest is a snapshot, not a tool. The purpose of building it is to answer what happens next, not to restate what already happened.
Structure it as inputs and outputs
The single most useful structural decision is to separate inputs from calculations.
Put every assumption in one block: new money raised, pre-money or post-money price, pool target and whether it sits pre or post closing, and the terms of each outstanding instrument. Everything else should be formulas referencing that block.
Then scenarios become columns rather than saved copies of the file. Change the inputs, compare the outputs side by side, and keep every scenario visible at once. Founders who model by editing numbers in place end up with several versions of the truth and no way to tell which assumption produced which result.
Give each row a label that names the holder and the instrument rather than an abbreviation you will not recognize in six months. Cap table models get reopened under time pressure during a negotiation, and legibility at that moment is worth more than elegance.
The interaction that catches people
Instrument conversion and pool expansion both change the share count, and each is often expressed as a percentage of the post-closing total. That makes them mutually dependent: the pool affects the total, and the total affects what conversion produces. Modeled independently and then added together, the result understates dilution. Model them in the same calculation, in the order the documents specify.
Where spreadsheets stop being adequate
A spreadsheet handles a simple company well. The signals that you have outgrown one are specific.
Instruments accumulating at different terms. Each one adds a conversion rule, and the combinations multiply faster than a hand-built model stays trustworthy.
Employees exercising options. Exercise timing, tax treatment, and partial exercises create a moving record rather than a static one.
Secondary transfers. Any transfer of shares between parties creates a chain of ownership your sheet was not designed to track.
More than one person answering ownership questions. Two people maintaining a spreadsheet produces two spreadsheets.
Rounding. Share counts are integers and percentages are not. Rounding decisions accumulate, and a model that quietly creates or destroys fractional shares will disagree with the official records in ways that are tedious to reconcile at exactly the wrong moment.
The model is not the record
This distinction matters more than any formula on the page. A spreadsheet is a tool for reasoning about scenarios. The authoritative record of who owns what is the set of signed documents: board consents, stock purchase agreements, option grants, and the instruments themselves.
So the model has to be reconciled against those documents rather than maintained as an independent source of truth. When they disagree, the documents govern and the spreadsheet is simply wrong, no matter how carefully it was built.
Use the model for what it is good at: understanding what a term sheet does to your ownership before you sign it, comparing two offers that look similar on the headline, and knowing what an exit at a given price actually returns to you. Those are decisions where an hour of modeling changes the outcome.
This is general information about modeling rather than legal, tax, or financial advice. Conversion mechanics, pool treatment, and preference structures vary by document, so confirm what your specific instruments say before relying on any model of them.
Frequently asked questions
- What should a cap table spreadsheet include?
- Current fully diluted ownership including options and unconverted instruments, the conversion of each outstanding instrument on its own terms, any option pool expansion and where it comes from, and an exit waterfall showing distribution at a given sale price after preferences. A model that only lists issued shares answers nothing useful.
- Why does my dilution math disagree with the investor's?
- Usually because instrument conversion and pool expansion were modeled separately. Both change the share count and both are often expressed as a percentage of the post-closing total, which makes them mutually dependent. Calculated independently and added, the result understates dilution in a direction that flatters the founder.
- When should you stop using a spreadsheet for a cap table?
- When instruments accumulate at different terms, employees start exercising options, any secondary transfer occurs, or more than one person needs to answer ownership questions. Rounding drift is another signal, since integer share counts and percentage targets disagree in ways that accumulate quietly and surface during diligence.
- Is a cap table model the official record of ownership?
- No. The signed documents are: board consents, stock purchase agreements, option grants, and the instruments themselves. A spreadsheet is a tool for reasoning about scenarios and must be reconciled against those documents. When the two disagree, the documents govern regardless of how carefully the model was built.