The Venture Capital Blueprint: Fund Returns Modelling
Building and Understanding Venture Fund Model.
Strategy and planning is always a key to any successful company and the vibrant world of venture capital, a realm where innovation and risk join forces to forge the future is no different. Surely it is a topic for the fund managers but broader audience may also be interested in this as grasping the nuances of how venture capitalists construct their fund’s financial frameworks shed some light behind strategies and what drives decisions.
Topic of ‘How to build a financial model for a startup’ garners widespread attention. An abundance of courses and tools are available to guide founders in developing budgets or financial models, with support from various agents and consultants. However, when the lens is shifted to focus on modelling a VC Fund’s cash flows / returns, the landscape changes drastically. This area is less explored with limited publicly available knowledge. While the foundational aspects of VC funds’ economics and compensation structures, like the ‘2 and 20’ model, are well understood, the difficulty of return modelling remain largely under wraps.
Hope this read will gear you with the necessary knowledge and tools to dive into the subject.
Instead of disclaimer
Financial model presented in this article is not intended to be used for predication of the returns of any fund or strategy.
There is certainly no ‘correct way’ to predict the future. Taking into account wide variety of alternative investment strategies and underlying assets, there is always a room for improvement of model’s precision and predictive power and we, ourselves, are constantly in the search for new methods and approaches (e.g. quite recently been analysing if the Monte Carlo method can be beneficial for our forecasts accuracy).
Financial model presented in this article can be used only for informational purposes only.
This model is nothing else but an instrument we use for approximate evaluation of the effect of certain investment parameters change on strategy’s perspective returns and funds portfolio.
Much like the sales projections for early-stage startups, accurately forecasting the exact returns of a VC fund is a complex endeavour, laden with numerous ‘ifs’ and ‘unknowns’ that are impossible to fully anticipate. However despite complexity process of returns modelling is important. It enables us to construct and comprehend the links between our own assumptions and the probable outcomes. Such understanding is crucial for identifying and monitoring the most critical elements of investment strategy, proactively measuring its sensitivity and, if necessary, adapting it within set limits.
First things first: template we will be discussing below is accessible through this link.
Construct of it as follows:
- Key assumptions are summarised in ‘Dashboard’ and ‘Cap. Table’ spreadsheets;
- Calculations of perspective portfolio dynamics are conducted in ‘Portfolio Calculation’;
- Results are summarized in ‘Dashboard’.
Assumptions
CapTable Dynamics
Model includes and based on Pro-Forma Captable of a startup — Funds potential (or current) target company.
It is built on the set of assumptions (e.g. valuations and sizes of the financing rounds raised by investee company, cadency, etc.) which are dependent on the industrial, geographical and other particularities as well as general state of investment and M&A markets.
To illustrate: recently French generative AI lab Mistral AI (formed by Meta and Deepmind alumnis) raised €105m seed round, hence assuming €1,5m Seed Round (as on the screen above) for AI startup won’t be correct, while it can perfectly fit the norm for different industry or GEO.
Tip: best to prove and sanity check these assumptions with up-to-date market statistics on the deals within Fund’s target segment(s) and parameters of previous deal closed. Take as much data as you can, and for that you can use services alike of PitchBook.
Investment Strategy and other assumptions
In addition to target companies’ CapTable dynamics, model takes into account:
(i) Set of General Assumptions
Key parameters of the Fund (size, fees structure, etc.) to be used for its top-line economics calculation.
*Management fee flow calculated within this model can be used a ‘revenue’ for the purposes of Fund’s budget development
(ii) Details of investment strategy
Parameters of initial investments (size, number of deals, cadency) and follow-on deals; linked and to be aligned with ‘Pro Forma Captable’.
(iii) Portfolio Dynamics
Assumptions related to target share of companies in portfolio which will be exited or written-off over the life time of the fund.
(iv) Exits
Assumptions related to target returns from exits (per portfolio company and stage): profitable, neutral or negative; to be aligned with ‘Pro Forma Captable’ dynamics.
Calculation
Model is built as a funnel with all above-described assumptions used to calculate a conversion of a ‘unit’ (portfolio company) from stage to stage of a funnel (from Funding Round to Funding Round), with some companies being exited (either with profit or written off) at each stage.
Follow-on investments also considerered here as portfolio companies progress to subsequent stages, along with various exit scenarios. For simplicity, we categorize exits into two types: ‘profitable’ and ‘unprofitable’. More intricate and accurate models could employ probability-weighted multi-parameters and other more sophisticated methods.
The model then utilizes the results of this funnel calculation, along with other inputted parameters, to generate its outputs. The key results of this strategic analysis are consolidated and displayed on the ‘Dashboard’ spreadsheet, providing a comprehensive cash-flow overview.
Refining the Details
In my experience, such level of details provides a perfect balance between model’s simplicity (model is easy to understand and modify; its parameters can be adjusted on the fly without complex re-calculations) and precision (required level of prediction power).
However, few important things were still missing.
The challenge is that most early-stage VCs, including ourselves, do not manage billion-dollar funds and thus number of companies in a single fund limited by its AUM will rarely exceed 30–40 names. Hence, rounding error might be pivotal for model outcome: e.g. if we assume 30% chance of conversion for cohort of 2 companies simple multiplication and rounding will lead to wrong result.
Solution we found was to replace multiplication with “yes/no” trigger. We used a built-into excel VBA (it’s literally just 1 line of code!) to randomise number from 1 to 100 and check if it’s above the assumed threshold (i.e. 20 for 20% write-off chance) to ‘activate the trigger’.
Code for this as follows :
X = 32
For i = 5 To X
Range("start").Offset(0, i).Value = 0
Range("start").Offset(1, i).Value = 0
Range("start").Offset(2, i).Value = 0
Next i
For i = 5 To X
Range("start").Offset(0, i).Value = Int((100 - 1 + 1) * Rnd + 1)
If Range("SeedWriteoff1").Value * 100 >= Range("start").Offset(0, i).Value Then
Range("start").Offset(1, i).Value = 1
Range("start").Offset(2, i).Value = 0
Else
Range("start").Offset(1, i).Value = 0
Range("start").Offset(2, i).Value = 1
End If
Next i- X is a number of periods;
- First loop is just a clean-up of a relevant range I6:AJ8;
- In the second loop [for each period, below on the example of the 1st period Q1-Y2] we:
(1) Set random value from 1 to 100 in cell I6, using VBA’s pseudo-random — Rnd function;
(2) Compare randomized value in I6 with D13 (20%, which stands for write-offs share in this example)
(3)Set relevant values in I7 (1 for write-off) and I8 (1 for conversion), which then used as a ‘trigger’ for further calculationsIn the excel model you will find separate functions (Sub’s) applying the same principle for the funnel and then, for exits modeling.
This approach allows us to correctly model the funnel for small number of companies via iterative checks if each of them will be converted, written-off or exited at each stage.
This approach allows us to correctly model the funnel for small number of companies via iterative checks if each of them will be converted, written-off or exited at each stage.
Consequently, the financial model’s accuracy heavily relies on the outcomes of randomisation. And in this case it’s prudent to evaluate the model’s robustness, ensuring that the range of outcomes from identical inputs remains within acceptable limits. To address this, we’ve introduced a ‘Sustainability Testing’ in the ‘Dashboard’ sheet. This section aggregates and reprocesses the results of eight recalculations (tests), utilizing an additional simple VBA macro for this purpose.
We support an opensource distribution of knowledge and welcome use of our templates. Please feel free to download Fund’s Financial Model — link described herein.
*For those who find it difficult to use and .xlxm template, we’ve developed ‘VBA-free’ version of the model available following the link. This version does not include capabilities described in ‘Refining the Details’ Section, but if you are having issues downloading or using macros-enriched version, please use VBA-free version or manually re-instal macros as described above.
**As this is open source critics and suggestions are highly welcome — if you have any ideas of how to improve this thing — please do reach out to us at action@verb.ventures
