Thursday 18 July 2024

One weird trick to solve cap table ESOP calculations

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.

The problem is that the number of shares allocated to the ESOP changes the total number of shares used to calculate equity percentages on a fully diluted basis. If you increase the number of shares in the ESOP, the total number of shares increases, so to get to your target percentage you increase the number of shares in the ESOP more, and the total number of shares increases, and so on. Eventually you reach an equilibrium that is close to your target ESOP percentage, but if you’re off by even one share then the percentage isn’t going to be a nice and round number.

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 SE is the number of shares to be allocated to the ESOP, SO is the number of shares allocated/issued to all others, and E is the targeted ESOP percentage. So for example:

The problem is that SE 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:


As long as the ESOP percentage is 0% or 100%, then this equation will work – note that in Excel it has to be a percentage (i.e. between 0% to 100% or 0.0 to 1.0). It may look a bit odd with the 1s appearing in the formula, but I promise that it does work. Let’s look at a practical example:

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: