Cap Table Modeling in Excel: Build a Pre/Post-Money Waterfall (2026)

April 24, 2026 · VeloraAI Team

Financial Modeling Excel Formulas

A cap table error at a Series A closing can silently cost a founder 2–5 percentage points of ownership — real money, sometimes eight figures at exit. Most founders and even early-stage investors manage their cap tables in Excel, and most of those spreadsheets are wrong. This guide walks through building a cap table model in Excel the way a venture banker would: clean structure, defensible formulas, SAFE/note conversions, option pool top-ups, and a working exit waterfall. Every formula below is copy-paste ready for Excel 365 or Excel 2021.

What Is a Cap Table and Why It Matters

A capitalization table is the master record of who owns what in a company — common shares, preferred shares, options, warrants, SAFEs, and convertible notes. It determines how equity is diluted in each round, how option grants are priced, and how proceeds are split at exit.

Three constituencies care:

Founders and employees: personal ownership and option dilution
Investors: ownership percentage, preferences, and exit returns
Lawyers and auditors: legal documentation of every issuance

💡 Pro Tip: Your cap table is a legal document before it is a spreadsheet. Match every row to a signed instrument — stock purchase agreement, option grant, SAFE, note — and store the document reference in the sheet.

What Should a Cap Table Include in Excel?

A clean cap table has six core columns and at least one row per security holder per security class. Start with a single tab called CapTable and build from there.

Column	Purpose	Example
Holder	Individual or entity	"Jane Doe", "Sequoia Fund XII"
Security Type	Common, Preferred Series A, Option, SAFE, Note	"Series A Preferred"
Issue Date	Grant or purchase date	2024-06-15
Shares / Units	Number issued (fully diluted basis)	1,250,000
Price Per Share	Issue price	$2.40
Invested Amount	Cash in (or strike × shares for options)	$3,000,000

From these six columns, every percentage, fully-diluted count, and dollar figure in the model derives automatically. Resist the urge to hardcode percentages anywhere.

Why Fully Diluted Matters

"Ownership percent" is meaningless without a denominator. In practice, three denominators exist and you need all three:

Issued and outstanding — shares actually certificated today
Fully diluted (narrow) — issued + all options granted + warrants
Fully diluted (broad) — narrow + unissued option pool + SAFE/note conversions

Investor term sheets almost always quote post-money ownership on a fully diluted broad basis. Founders who calculate on narrow get a nasty surprise at closing.

How Do You Build a Pre-Money Cap Table in Excel?

Start with what exists today before any new round. Assume a hypothetical seed-stage company with two founders, an employee option pool, and prior SAFE investors.

Step 1: Lay Out the Rows

A              B                  C          D              E
Holder         Security           Shares     Price/Share    Invested
Founder 1      Common             4,000,000  0.0001         400
Founder 2      Common             4,000,000  0.0001         400
Option Pool    Options (unissued) 1,000,000  0              0
Key Hires      Options (granted)  500,000    0.10           50,000
SAFE Inv A     SAFE               -          -              500,000
SAFE Inv B     SAFE               -          -              1,000,000

Notice the SAFEs have no share count — they convert at the next priced round. We will model that in Step 3.

Step 2: Calculate Fully-Diluted Shares and Ownership

In column F, compute fully-diluted shares (treat SAFEs as zero for now — they convert later):

F2: =IF(B2="SAFE", 0, C2)

In column G, compute ownership percentage:

G2: =F2/SUM($F$2:$F$7)

Format G as a percentage with two decimals. The sum of column G must equal exactly 100.00% — if it does not, you have a rounding or reference bug.

⚠️ Warning: Never use hardcoded percentages. If Series A adds 2,500,000 shares, every percentage in the sheet must update automatically. Static percentages are the #1 source of cap table errors at closing.

Step 3: Add a Summary Block

Above or beside the main table, add a summary using SUMIFS:

Total Common:       =SUMIFS(C:C, B:B, "Common")
Total Preferred:    =SUMIFS(C:C, B:B, "Series A Preferred")
Total Options:      =SUMIFS(C:C, B:B, "Options*")
Total Raised ($):   =SUMIFS(E:E, B:B, "<>Common")

The SUMIFS pattern makes the summary bulletproof even as you add more holders or security types — SUMIFS, along with XLOOKUP, XIRR, and INDEX/MATCH, is one of the essential Excel formulas every financial analyst should master for building clean, maintainable models.

Modeling a Priced Round: Pre-Money to Post-Money

This is where most Excel cap tables break. The core identity:

Post-Money Valuation = Pre-Money Valuation + New Investment

And:

New Investor Ownership % = New Investment / Post-Money Valuation

Suppose the company is raising $5M at a $20M pre-money valuation. Build a new block:

Pre-Money Valuation:   20,000,000
New Investment:         5,000,000
Post-Money Valuation:  =B1+B2                 → 25,000,000
New Investor Share %:  =B2/B3                 → 20.00%
Price Per New Share:   =B1/(existing FD shares) → depends on FD count
New Shares Issued:     =B2/PricePerShare

Example: If pre-money fully-diluted shares total 10,000,000 and pre-money valuation is $20M, the price per new share is $2.00. The $5M investment buys 2,500,000 new preferred shares, taking the post-money FD count to 12,500,000. The new investor owns 2,500,000 / 12,500,000 = 20.00% — matching the ratio above.

Flow of a Priced Round

graph TD
    A[Pre-Money Cap Table] --> B[Agree Pre-Money Valuation]
    B --> C[Investor Commits Amount]
    C --> D[Calculate Price Per Share]
    D --> E[Convert SAFEs and Notes]
    E --> F[Top Up Option Pool if Required]
    F --> G[Issue New Preferred Shares]
    G --> H[Post-Money Cap Table]

Cap table Excel funding round flow: pre-money inputs convert SAFEs, top up option pool, and issue preferred to reach post-money.

How Do SAFEs and Convertible Notes Convert in Excel?

SAFEs (Simple Agreements for Future Equity) convert into preferred shares at the priced round. Each SAFE has a valuation cap, a discount, or both. The SAFE converts at the better of these two prices for the holder.

Conversion Formula

For a SAFE with a $10M cap, 20% discount, raising into a $20M pre-money round with $2.00 per share:

Cap Price    = Valuation Cap / Pre-Money FD Shares (pre-SAFE)
             = 10,000,000 / 10,000,000 = $1.00

Discount Price = Round Price × (1 - Discount%)
             = 2.00 × 0.80 = $1.60

Conversion Price = MIN(Cap Price, Discount Price) = $1.00

SAFE Shares = SAFE Investment / Conversion Price
           = 500,000 / 1.00 = 500,000 shares

In Excel, with SAFE details in columns B through E:

Cap Price:        =IF(B2=0, 1E9, B2/PreMoneyFDShares)
Discount Price:   =IF(C2=0, 1E9, RoundPrice*(1-C2))
Conv Price:       =MIN(CapPrice, DiscountPrice, RoundPrice)
SAFE Shares:      =InvestedAmount / ConvPrice

The 1E9 (one billion) trick makes a missing cap or discount effectively infinite, so MIN picks the valid one without nested IF logic.

ℹ️ Note: Pre-money SAFEs dilute new investors along with founders. Post-money SAFEs (YC's default since 2018) dilute only pre-existing holders — the new investor's post-money percentage is locked. Check which flavor you signed.

Convertible Notes

Notes add accrued interest to the principal before conversion:

Conversion Amount = Principal × (1 + Interest Rate)^Years
Note Shares = Conversion Amount / Conversion Price

For a $1M note at 6% interest held for 18 months converting at a $1.50 price:

=1000000 * (1.06)^1.5 / 1.50   → 671,094 shares

Option Pool Top-Ups: The Tricky Part

Investors almost always require a pre-money option pool top-up — the pool is expanded before their investment, so existing holders (founders, prior investors) absorb the entire dilution. This is often the largest hidden cost for founders.

The Math

If the post-money pool target is 15% and the current pool is 5% of post-money fully diluted:

New Pool % of Post-Money = 15%
Existing Pool Shares     = current pool
Required Pool Shares     = Post-Money FD × 15%
Pool Top-Up Shares       = Required - Existing

The top-up is issued before the new price is calculated, so the denominator for the new investor's price already includes the expanded pool. Founders get diluted twice — once by the top-up, once by the new round — while the investor is diluted only by the new round.

⚠️ Warning: Never let the option pool top-up calculation flow after the new share issuance in your model. The pre-money top-up must be included in the pre-money fully diluted count used to derive the price per share.

Formula for Founders' True Dilution

Founder Post-Money % = Founder Shares / Post-Money FD Shares
Founder Dilution %   = (Founder Pre-Money % - Founder Post-Money %) / Founder Pre-Money %

A founder starting at 40% pre-money who ends at 30% post-money has been diluted 25% on a relative basis — not 10 percentage points. Both numbers matter; investors track absolute percentages, founders feel the relative loss.

Building the Exit Waterfall

At an exit event — acquisition, IPO, or secondary — proceeds are distributed per the liquidation waterfall. Preferred shareholders get paid first, then common. Excel handles this cleanly with a tiered MIN/MAX structure. In PE-backed companies acquired through a leveraged buyout, the exit equity value entering this waterfall is first sized by the LBO model's returns analysis — the cap table waterfall then determines how that gross exit proceeds split between preferred investors, option holders, and founders before the PE fund's carry economics come into play. For founder-led exits through strategic M&A or IPO, the enterprise value entering the waterfall is typically established through a DCF model in Excel or a comparable company analysis benchmarking the exit price against what similar businesses trade for in public markets — either approach sets the enterprise value that flows down through the cap table.

Step 1: Liquidation Preferences

Most preferred shares have 1x non-participating preference: the holder takes the greater of (a) their investment back, or (b) their pro-rata share as if converted to common.

Preferred Payout = MAX(Investment × LiqPrefMultiple, ProRataConvertedValue)

In Excel, for a Series A holder who invested $5M with 1x pref, with company exit at $50M and the holder's 20% fully-diluted stake:

Investment:       5,000,000
Liq Pref Mult:    1
ProRata Value:    =ExitValue × StakePct    → 50M × 20% = 10M
Payout:           =MAX(Investment*LiqPrefMult, ProRataValue)  → 10M

At a $15M exit, the same formula yields:

ProRata Value:    =15M × 20% = 3M
Payout:           =MAX(5M, 3M) = 5M   (holder takes the preference)

Step 2: Common Residual

After all preferred preferences pay out, common shareholders split the remainder pro rata:

Common Pool = Exit Value - SUM(Preferred Payouts)
Common Per Holder = Common Pool × (Common Shares / Total Common)

Step 3: Participating Preferred

If any preferred is participating (also called "double-dip"), those holders take the preference and share in the common residual on a converted basis. Add a toggle column and use IF:

Payout = IF(Participating, 
            Investment + (ConvertedShare × (ExitValue - AllPreferences)),
            MAX(Investment, ProRataValue))

💡 Pro Tip: Always model both a non-participating and participating scenario side by side. The delta between them at different exit values is exactly what founders should negotiate away in the term sheet.

To systematically compare how non-participating and participating structures affect founder and investor returns across a range of exit values, build a two-way sensitivity table in Excel — vary exit valuation on one axis and liquidation preference multiple on the other to show exactly where each structure breaks even.

How Do You Track Multiple Financing Rounds Over Time?

Build one tab per round — Pre-Seed, Seed, SeriesA, SeriesB — and a master Rollforward tab that references each. The rollforward has one row per holder and one column per round showing cumulative shares and ownership.

Use VLOOKUP or XLOOKUP to pull ending positions from each round into the rollforward:

=XLOOKUP(Holder, SeriesA!Holder_Range, SeriesA!Shares_Range, 0)

The 0 default is critical — it ensures holders who did not participate in a round show zero rather than an error. For a complete reference on XLOOKUP's syntax, lookup direction, approximate matching, and dynamic array returns, see our XLOOKUP guide for financial analysis.

Linking Rounds With Named Ranges

For a large model, define named ranges like SeriesA_FDShares and SeriesA_Price rather than cell references. This keeps formulas readable when you return to the model three rounds later.

Frequently Asked Questions

Can I build a cap table in Excel or do I need Carta?

For a company with fewer than ~30 holders and straightforward instruments, Excel is faster and gives you full visibility into the math. Beyond that, or once you have ISOs with 83(b) elections and vesting cliffs across 50+ employees, dedicated software like Carta, Pulley, or Cap Table Pro pays for itself. A common pattern is Excel for modeling future rounds and scenarios, Carta for the legal source of truth.

What is the difference between pre-money and post-money valuation in Excel?

Pre-money valuation is the company's value before the new investment. Post-money is pre-money plus the new investment amount. In a cap table, post-money valuation sets the denominator for the new investor's ownership percentage: New Investor % = Investment / Post-Money. All ownership percentages after a round should sum to 100% of the post-money fully diluted count.

How do I model a SAFE with both a cap and a discount in Excel?

Calculate two prices. The cap price is the valuation cap divided by pre-money fully diluted shares. The discount price is the round's price per share times one minus the discount rate. The SAFE converts at the lower of the two — that gives the SAFE holder the better deal. In Excel: =MIN(CapPrice, DiscountPrice, RoundPrice).

What is the 83(b) election and does it affect the cap table?

The 83(b) election lets founders pay ordinary income tax on restricted stock at grant (when value is near zero) rather than at vesting. It does not change the cap table structure — shares appear regardless — but it is critical to track the election filing date per founder in a notes column. Missing the 30-day filing window is a common and costly mistake.

How do I show vesting on a cap table in Excel?

Add two columns per option or restricted stock grant: Vesting Start and Cliff Months. Calculate vested shares with a formula that enforces the cliff:

=IF(DATEDIF(VestingStart, TODAY(), "M") < CliffMonths, 0,
    MIN(TotalShares, TotalShares × DATEDIF(VestingStart, TODAY(), "M") / VestingMonths))

This returns zero before the cliff, then vests monthly up to the total grant.

Closing: From Spreadsheet to Source of Truth

A cap table is the spreadsheet a founder opens most often after the P&L. Building it properly in Excel — with dynamic formulas, fully diluted math, and scenario-ready waterfalls — gives you negotiating leverage at every round. The company-level liquidation waterfall built here determines who receives what at exit; in PE-backed transactions, those same proceeds then flow through a fund-level PE distribution waterfall in Excel that allocates between GPs and LPs across preferred return, catch-up, and carried interest tiers. Tools like VeloraAI can accelerate the modeling work itself, generating conversion formulas and exit waterfalls from natural language prompts inside Excel, so you spend your time on the deal terms rather than on debugging a SUMIFS.

Next step: build the pre-money tab from your current holdings today. Add one priced round scenario and one exit scenario tomorrow. Within a week you will have a model that flexes with every new term sheet you receive.