A common challenge for capitalisation table modelling is the requirement for a fixed ESOP percentage post-investment. It’s hard to figure out exactly how many share-equivalent options should be allocated to that pool, when the valuation is changing, when the amount of capital raised is changing, when new shares are being issued and/or some old shares are being cancelled.

The standard experience of figuring out the ESOP pool size is:

- Our proposed lead investor says we need to have 10% of equity post-investment allocated to an ESOP pool so that we can sufficiently incentivise staff and new hires.
- We can model the current number of issued shares plus the number of new shares that will be issued as part of the capital raise, to calculate the total number of shares post-investment.
- So if we take 10% of that number and allocate that to an ESOP... wait why does the spreadsheet tell us this is only 9.1% of the post-investment equity?
- If we just keep trying differnt numbers... yeah that’s close enough to 10% to call it a day.

But there
is a solution! It just requires some math... and you’ve already got a
spreadsheet in front of you so the math shouldn’t scare you off. If we look at
the first principles method of calculating the ESOP share allocation, we can express
it with this formula:

Where S_{E}
is the number of shares to be allocated to the ESOP, S_{O} is the
number of shares allocated/issued to all others, and E is the targeted ESOP percentage.
So for example:

The problem
is that S_{E} appears on both sides of the equation, and Excel doesn’t
like that and will give you the dreaded circular reference error. But you can
do some algebraic rearrangement to solve this:

So in E9 we can
enter the formula =SUM(F3:F8)/(1/G1-1) and hey presto...

And now we
can change the pre-money valuation...

Or change
the amount of capital being raised...

Or change
the target ESOP pool size...

Or just
have some really weird numbers that are not round at all...

It shouldn’t
take a PhD to do some algebraic manipulation, but I keep seeing cap table spreadsheets with
hardcoded ESOP guesses that immediately break when you change any of the
high-level parameters. So if you’ve read this far, just use this formula and everyone
will have more flexible spreadsheets!

**=SUM(“range of cells containing other shares”)/(1/”ESOP
percentage”-1)**

P.S. What if
there are already ESOP shares issued? Just subtract the existing number of ESOP
shares from the outcome of the formula, and leave that in the New Shares
column: