Free Excel templates

A free pro rata and dilution calculator in Excel: what defending ownership costs

An implied cheque from fund size and reserves, the pro rata cheque in two follow-on rounds, the same company defended or diluted, the exit at which follow-on dollars break even, and whether a 1:1 reserve binds: eight sheets, no macros.

The file tests a growth equity fund of 600 with 15 positions and a 1:1 reserve: an implied first cheque of 20 for 15% of a company. Two later rounds, at step-ups of 2.5 and 2.0, would dilute that stake to 10.2%. Defending it costs 25 more, 1.25 times the first cheque. At an exit of 1,500 the defended stake returns 5.0 times against 7.65 undiluted, for almost the same IRR, and below an exit of 521 the follow-on dollars lose money outright.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Pro_Rata_and_Dilution_Calculator.xlsx (25 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from The Growth Equity Investor. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Eight sheets. Every input is on sheet 1.

0. Read Me
The four things the arithmetic says.
1. The Inputs
Fund size, positions, reserve ratio, entry ownership, the two rounds' step-ups and dilution, and the exit.
2. The Fund
Initial and reserve pools, the implied cheque and entry valuation, and the two rounds.
3. The Pro Rata Cheque
The pro rata in each round as a multiple of the first cheque, and when it exceeds it.
4. Defended or Not and 5. Where Defending Loses
Ownership, capital, proceeds, multiple and IRR under both policies, and the exit values at which defending adds or destroys value.
6. Reserves or Picking Winners and 7. Checks
Whether a 1:1 reserve binds at fund level, and the checks.

What the worked case returns

MeasureValue
Implied first cheque20 for 15%
Pro rata cheques, round C and round D10 and 15
Ownership at exit, undefended10.2%
Multiple at an exit of 1,500: undefended, defended7.65x, 5.0x
IRR: undefended, defended40.4%, 40.8%
Exit at which follow-on dollars break even521
Reserve used by pure defence48%

Defending lowers the multiple at every exit value, because the follow-on dollars go in at higher prices than the first cheque. Whether it is worth it depends on the exit, and the break-even exit of 521 is still a company worth 3.9 times its entry valuation.

How to use it on your own fund

  1. Enter fund size, positions, reserve ratio and target ownership on sheet 1.
  2. Enter the step-up and dilution of each follow-on round.
  3. Read the pro rata cheques and the defended against undefended returns.
  4. Check the break-even exit before reserving for every position.

Questions people ask about it

Is this template really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference.

How is a pro rata cheque calculated?

Your ownership times the size of the round, where the round size is the share of the company sold times the post-money valuation. Here 15% of a round of 66.7 is 10, and 15% of 100 is 15.

Can I use it for my own fund?

Yes, by replacing the inputs. The fund and the company are illustrative.

The rest of the files

This template is the companion workbook for The Growth Equity Investor; the companion page also carries forty self-marking questions on the same material. Free, like this one.

Open the companion files →

Other free templates

This template comes from The Growth Equity Investor. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.