Deterministic Unissued Options Pool Calculations

When investors write term sheets for investments in companies, they often state that the price per share of an investment is inclusive of an X% unissued options pool on a post-closing fully diluted basis. The percentage of X% is often negotiated significantly, and I see it usually falling between 8% and 12%. It seems like a simple statement in legalese, but the price per share of a round is now self-referential and recursive based on this totally standard deal term definition. Why is it self-referential? Because price per share is calculated as follows:

(PPS is the acronym that I’m using for Price Per Share)

and then,

(PCFDS is the acronym that I’m using for the Post Closing Fully Diluted Shares, which is a fancy phrase to say all the shares that are issued, include all options and warrants exercisable, once the financing is complete)

and then,

and then,

Do you see the problem here? Each of the lower formulas contain references to variables in the formulas above them, so these formulas in combination create a circular reference that cannot be solved deterministically in its current form.

Most VC associates encounter this problem in their first month on the job when they are asked to make their first pro-forma cap table model. They get hit by Excel with a circular reference warning. They then Google the warning and figure out that they can turn on the “Iterative Calculations” feature in the settings panel of Excel in overcome this error. Unfortunately at this point two issues emerge.

1. The pro forma cap table is now somewhat unstable and attempting to edit the wrong number at the wrong time can cause the error #REF to pop up in cells that reference around upon themselves. Hitting CTRL+Z in this state will not successfully undo the last calculation and you will have to close the doc and open a prior saved version to recover from this error. I’ve lost hours of work to this issue.
2. Anyone that opens this spreadsheet who does not have “Iterative Calculations” enabled will see a broken spreadsheet, see a circular reference error upon loading, and will not be able to edit the sheet in any functional way until they change their preferences. It’s not a good experience for a viewer expecting to see a model that is supposed to accurately define a multi-million dollar equity financing.

These problems can be avoided by building the pro forma cap table model in a way that does not reference upon itself. This requires some algebra legwork to accomplish. I did this recently and am writing this blog post to benefit other people that need to do this calculation in the future.

Definitions:
Variable Name = Description (Units)
PreShares = Current Shares Outstanding Excluding NewPool
Target% = Target Post-Closing Unissued Options Pool Percentage (%)
Investment$ = Total New Money Invested in This Round ($)
Pre$ = Pre Money Valuation in This Round ($)
OldPool = Unissued Options Pool Available Before Financing (Shares)
NewPool = New Shares in Pool to Hit Post-Closing Target % (Shares)
InvestmentShares = New Shares Issued to Investors in Financing (Shares)
PPS = Price Per Share of Financing ($/Shares)
PCFDS = Post Closing Fully Diluted Number of Shares (Shares)

We want to solve for NewPool. We are only allowed to use OldPool, Target%, Investment$, PreShares, and Pre$ to define NewPool.

Why only these variables? Because they don’t actually vary at all in a typical pro forma cap table. OldPool, Target%, Investment$, PreShares, and Pre$ are all known numbers or defined deal terms that are fixed in a term sheet that do not need to be calculated. You know all these numbers at the start of making a pro forma cap table. By contrast, the variables InvestmentShares, PPS, and PCFDS are all dependent on the value of NewPool. If our definition of NewPool references any of InvestmentShares, PPS, and PCFDS then we will create a circular reference, and this exercise fails.

So, lets begin with our attempt to calculate NewPool. The recursive approach is to based on NewPool’s relationship to the Target%, which is defined as follows:

At the start of this exercise, I said that the name of the game is solving for NewPool, so lets rearrange that formula to:

Great, now we need to define PCFDS in terms that aren’t dependent on the value of NewPool. Another way to define PCFDS is as follows:

This is progress in that we have decomposed PCFDS into some known numbers, but InvestmentShares is still a term that depends on the value of NewPool, so we’ve fixed one problem, but added another one. So, lets look for an alternative definition of InvestmentShares to substitute:

OK, again, we made progress, but introduced a new problem, because PPS also depends on the value of NewPool. Lets think further about the definition of PPS. Here’s an alternative:

Alright! All of the variables in that definition are known legal terms for defining a pool, so now we need to take this system of 4 linear equations and reduce them down into a single formula that defines NewPool alone using only known variables. Lets start by rolling back up the chain of equations and insert our definition of PPS into our definition of InvestmentShares:

OK, and lets make the denominator less ugly by simplifying the expression:

That’s a little cleaner. Now lets roll this safe definition of InvestmentShares up the chain again into the definition of PCFDS:

Great! That’s a legal definition of PCFDS that contains no references to calculated variables. We can roll up the final step of this system of linear equations and define NewPool using this new definition of PCFDS:

This is exciting, but it’s not victory yet, because we don’t want our definition of NewPool to reference itself. We need to reorganization this formula such that a single “NewPool” is alone on the left side of the equation and everything that defines it is on the right side. This took me a good amount of algebra to accomplish (roughly 12 steps), though nothing was ever more complicated technically than 8th grade math class. The formula above simplifies to:

And that’s it! If you can map your Excel pro forma cap table’s cells against my semantic definition of these terms, then you can plug this formula into the cell that defines how many shares you need to add to the pre money options pool in order to achieve your target post-closing unissued options pool percentage in a completely deterministic way.

If you’d like to just use a cap table template that already has this wired up, I’ve created one for the purpose of this post. It’s based on eShares round modeling template, so hat tip to the eShares folks for their contribution here. To the small handful of VC associates that made it to the bottom of this long mathy inside-baseball post, cheers!

PS: This was my first time experimenting with LaTeX. Not as painful as I expected.