In Part A of our liquidation model series we walked through the basic setup of an example liquidation model and discussed how to evolve model sophistication over time.
Now in Part B we expand upon that initial model to incorporate 4 tranches of common options to simulate employee grants over time. This will include expanded recursive (iterative) functionality to more properly calculate the dollar outcomes for all share classes. To refresh yourself on the fundraising synopsis, please revisit Part A.
Let’s take a look at the Excel model (available in Downloads – File: Liq_Model_PartB_v2.xlsx).
It might be easier to trace the changes side-by-side if you can look at both the models from Part A and Part B at the same time. Again, see Downloads.
What we noticed in Part A: One of the most obvious errors in our model in Part A was that it did not take options strike price into account. So all issued and unissued options were sort of treated as common stock.
This might be ok for planning purposes, possibly for roughly mocking cap table scenarios and how they affect liquidation distributions. But now that we have more information about the options tranches, we can incorporate better logic and more accurate per share calculations. Increasing the “correctness.”
The Conceptual Problem: Thinking through this, price per share depends on the liquidation proceeds available to equity holders divided by the number of shares that exist at the time of the sale. Subject to liquidation preferences, etc.
But the number of shares that exist can vary depending on how many option holders exercise their options and how many preferred shareholders decide to convert to common shares.
And both of those decisions depend entirely on what the price per share available to common shareholders is. Ipso facto, we’ve created a logic loop.
The Solution: Luckily for us, Excel has a way to deal with this loop: the often maligned and always ominous circular reference (Figures A & B). Starting with our original model from Part A, to create this loop, conceptually what we did was to:
- Keep the liquidation preference logic in place
- Add a ‘Residual Available to Common’ calculation (Figure B, Row 61)
- Add a section for Common Stock & Options, so we know when both preferred shareholders convert and option holders exercise.
- Add a ‘Price per Common Share’ calculation (Figure B, Row 75)
- Relink the liquidation preference section to work off of the ‘Price per Common Share’ calculation (step 4) – this creates the circular reference.
- Trim the liquidation preference section, eliminating the usual residual per remaining share logic
- Rebuild the Equity Proceeds section from the new calculations
Figure A – Example circular reference, created on top of Part A model, transitioning to Part B model
Figure B – Circular reference built into Part B model
Iterative Loops: The final step to make this work is to turn on iterative calculations. In excel go to File → Options → Formulas → Calculation Options. And check the box labeled ‘Enable iterative calculation’. A maximum iteration of 100 should work, but you can increase to 1000 to be safe. Click Ok.
Figure C – Enable iterative calculation
Results: So did it work? Let’s compare the Equity Proceeds per share in Part A vs Part B in the figures below (Figures D & E respectively).
Figure D – Equity Proceeds per share from Part A Model
Figure E – Equity Proceeds per share from Part B Model
As you can see, instead of one monolithic ‘Common Stock & Options’ price per share we now have 5 rows, nicely splitting out the per share values for each tranche of common stock and options. Notice the strike prices are correctly taken into account (assuming these are paid out net in a liquidation event). And also importantly, the values for some of the preferred shareholders are now more correct, because of the increased accuracy related to incorporating options strike prices and the knock-on effect on conversion event timing.
The Downside: So we did a good job, right? Sure. But let’s not forget certain downsides. Namely this model now depends on iteration. So 1) it’s a little bit brittle. If you make certain cell reference changes, the whole thing can blow up and you get a sea of lovely ‘#DIV/0!’ errors. And you can’t simply press undo, you have to close it and start your changes over. (So save frequently.)
And 2) if you ever send it to a colleague, when they open it, a ‘circular reference’ warning will pop up. And then you’ll have to explain that no, you didn’t make a mistake and that they need to turn on iteration in Excel. But only for this workbook, not others. No, it’s not that weird. Etc.
Nonetheless, these small pains can be worth the gain! Please let us know in the comments section if you found this helpful or what other approaches you’ve taken.