Changelog

Two updates to the Cap Table and Exit Waterfall Tool:

Distributing proceeds to unconverted SAFEs and convertible notes #

Added Exit Waterfall, with Convertibles sheet to add new section to handle the distribution of proceeds to unconverted convertibles in a liquidation scenario prior to a conversion event. This is a beta feature and will be actively tested, and please note that it is always important to read the convertible agreement for how to handle a liquidation event.

This change is to address the complications with handling how proceeds are distributed to convertible instruments that have not yet converted to shares. The calculations are different depending on the type of convertible, and the iterative calculations required can create difficulties if there are a lot of convertibles, but the goal here is to create a structure that's easier for more people to use.

• Added new section to Exit Waterfall sheet to show the distribution of proceeds to cash and securities, if the distribution includes securities.

This was always possible to do using the distribution of proceeds details, but the new feature is to just make that simpler and more apparent to users with this situation.

The next live session Cap Table and Exit Waterfall Masterclass will start in September (cohort 16!) and is now open for registrations. Couple quick notes:

If you're a startup executive, investor, head of finance, lawyer, or accountant that works with startups and wants to understand how venture investment structures impact ownership and returns, this class covers in detail the structures and their impact on cap tables and liquidity distributions.

You can register here, I'd love to see you in class.

Unsure if the course is a fit for you or whether you can make the live sessions?

• Want to understand how I teach? Beginning Cap Tables covers the first part of the session of the Masterclass and gives you an idea of how I approach the material. (The content gets more complicated, obviously, but this gives a good intro.)
• Unable to join the live sessions? Check out the Cap Table and Exit Waterfall Course, which covers the same material using recorded videos that you can access anytime, with access to the same instructional slides and example spreadsheets, and I'm available for Q&A by email anytime.
• Want to chat about the content or course? Email me anytime or set up a time to chat to discuss your specific situation. The course material will be similar to past cohorts, infused with a few new learnings from my recent new rolw as a fractional CFO for a venture fund.

Small update to the Venture Capital Model:

1. Edited the calculations for tiered carry for each LP class; the calcs for the middle "proceeds applicable for carry" used the distributions from the last period instead of cumulative distributions as of the last period, meaning it did not account for past distributions and any tiered carry for certain returns multipls was not applied. The edit is for all three tiered carry calculations for each LP class.

As always, questions, ask anytime.

Hello spring update to the Venture Capital Model (renamed from Venture Investor Model):

1. Fixed a couple intermediate change issues in draft versions of the model post the last detailed update, all removed from the live version.
2. Edited Management Company sheet to reflect Quarters, not Years (the timescale should have said Quarters and should have been 80 periods).
3. Edited recycling calculations on Forecast to eliminate possibility of calculating recycling larger than possible with proceeds in a given periods with certain recycling structure options (added a min() test to a few lines).
4. Edited % of Invested Capital per stage calcs on Get Started to correctly handle late-stage investments (previously did not account for the last two stages correctly).
5. Edited the scenario 2 and 3 Forecast sheets to bring them to parity with the changes to the LP waterfall.
6. Edited Get Started to provide more detail behind the average investment to make the difference between the number of investments and the invested capital clearer.
7. Brought back the option to use called capital as a base for management fees. This had been deprecated due to the complexity of calculating this without circulars, but was added back in using algebra method built into the Forecast sheet.
8. Edited Get Started for new method to forecast overall invested capital, and edited Forecast to create the algebra to forecast the base for investments using assets under management and called capital. This was a heavy update, impacting management fees and deployment subcalcs. It will not always calculate a perfect called capital = committed capital under all scenarios, but is more reliable and still reflects the practical difficulties for funds budgeting for investments and fees with some of the recycling and fees options they may be using in their LPA. Someday I'll get the algebra perfect, until then we will live with a real life scenario.

As always, questions, ask anytime.

Small edit in the Cap Table and Exit Waterfall Tool:

• Edited Cap Table, Anti Dilution to adjust calculation of broad-based and narrow-based weighted average so it uses the amount invested in the new round, not the amount invested by each of the existing shareholders in past rounds, in the calc for the new conversion price

Significant update to the Exit Waterfall in the Cap Table and Exit Waterfall Tool:

• Edited the Exit Waterfall, Simple sheet to add in features previously only in the base Exit Waterfall and Exit Waterfall, Extended sheets, bringing this stucture to match the features in the previous Exit Waterfall sheets, and deleted the older sheets and renamed the Simple variant to just Exit Waterfall. This was done because the Simple version is built to use an easier formula structure than the pre-existing Exit Waterfall and Exit Waterfall, Extended sheets, and because of that it is easier to use because (1) the feature toggles to change the formulas based on the situation are no longer needed, and (2) is much easier to extend to cover more share classes and options. Previously it was very hard to extend the Exit Waterfall sheet to cover more than the prebuilt number of share classes, now it is 100x easier to extend it for any number of share classes, options with different strike prices, or warrants with different exercise prices.

Simpler, clearer,better, always innovating.

Please note that the Exit Waterfall, like the previous versions, is not built to handle unconverted convertible notes or SAFEs at exit, but can be done with a custom edit. Please contact me if you are interested in that.

Major update to the Venture Capital Model (renamed from Venture Investor Model):

1. Portfolio Construction. Major rework to use the same portfolio construction method as the Venture Capital Model, Average Cap Table version, moving the prior Portfolio Construction inputs to the Get Started sheet and expands the number of rounds modeled from 4 to 6. This new approach allows you to model first checks at multiple entry points automatically (e.g. first checks at Seed and Series A, and then optional different follow-on strategies per each first check strategy). This is a major upgrade from previous versions which required a more complex approach to modeling multiple entry points (and where I offered different versions of the model for single-entr and multiple-entry strategies), and thus the previous multiple-entry version has been thus been deprecated.
2. Scenarios. Adds in the Scenarios sheet to evaluate multiple return scenarios simultaneously. This was prebuilt in many of the venture capital models but had not yet been built into this version.
3. Fund Waterfall. Adds in a new Waterfall section to the Forecast sheet to allow the model to handle up to three different LP bases with different return structures involving different preferred returns, return of capital, GP carry, and carried interest assumptions. In addition to this adding more detail to the LP return calculations and allowing for differential fee structures, this allows the model to handle scenarios where the fund has grants, philanthropic investors, or "catalytic" limited partners that may want no return of capital, limited returns, or different return expectations from the rest of the LP base. (Caveat that (a) currently different management fee rates for the LP bases is not supported at the detailed waterfall level, and (b)this waterfall currently only supports the case where all LPs are pari-passu to each other. Detailing the management fees by LP base and adding in LP seniority to the fund waterfall is next in the queue.)
4. Tracking. As part of this update the "Actuals Tracking" variant was not updated and has been temporarily deprecated. I anticipate working that feature into a later update.

Small update to the Venture Capital Model, Average Cap Table:

• Slight cleanup on Get Started to (a) change the calculation of average invested per company at exit for later rounds and (b) adjust the capital allocation to different stages, as well as edits on Forecast sheet to adjust the unrealized gains calculation.

Update to Venture Capital Model, Quarterly Forecast:

• Edited Forecast sheet and Get Started sheet to eliminate the circular reference that would happen if you choose to recycle using new and follow investments option on Get Started. The edits are in the calculations for deployment section on Forecast and a new set of calculations around capital allocation in the Portfolio Construction section on Get Started.

Update to Cap Table and Exit Waterfall Tool:

• Edited Exit Waterfall and Exit Waterfall, Extended sheets to fix how warrants are exercised cashless. Previously the cashless exercise would result in the proceeds distribution check being off because the foregone proceeds from the cashless exercise was not accounted for going back to other shareholders. The method was changed to adjust the number of shares issued tot he warrant holders by the value of the foregone proceeds by the cashless exercise, if that option is selected. Thank you Preten for finding this.
• I also adjusted the Exit Waterfall, Simple sheet for calculating proceeds to warrants; previously the method subtracted out the foregone proceeds from warrant holders and added them back to the other sharedholders, which resulted in the same result, but I adjusted the calcs to use the same method of adjusting the shares so that the methods are consistent.

Today I released an update to the Venture Studio Model that with edits to Total Investments, by cohort of New Investments, Proceeds, Writeoffs, and Invested Capital Exited, to not double count the proceeds from the investments in the studio, which flow through the studio and not the fund. Thank you James for finding this.