This is part 3 of a series to help you use your venture investor model template. Here’s the full series:
* * *
You’ve got your model all set. So, what is going on beyond the assumptions, and how are the proceeds and returns calculated?
Calculating Proceeds, Returns and IRRs
The model actually calculates the proceeds twice: once on the Portfolio sheet, where you can see all the details around the fund construction, and second, on the Operating Forecast sheet. Why?
Essentially, on the Portfolio sheet it’s calculating the aggregate proceeds and gains, while in the Operating Forecast sheet it’s allocating the aggregate investments, proceeds, and cash flows over the investment and management timeframes. The fact is that building a model that forecasts aggregate returns is actually fairly simple, but building a model that calculates returns based on timeframes (IRRs) is quite harder, and involves some thinking about what you’re forecasting.
You’ll notice that the model forecasts investments and proceeds at an overall fund level, not an individual company level. So how can we forecast the aggregate portfolio without forecasting each investment?
Think about how funds make their money - a small set of outsized exits drive the vast majority of overall returns. So, the existance of large multiple exits, 25x or greater, are incredibly important to a fund’s overall performance. But how can you forecast the timing and distribution of highly infrequent and random events, and what kind of impact will it have on your return estimates?
In an earlier version of this model I created a forecast of individual investments using probability distributions of exits, follow-ons, and going out of business, as well as the timing and exit multiple of exits at different stages, and one could use the model to run a set of observations of portfolio performance to create an average of the results. But I eventually simplified the approach by stripping out the random generation and creating a methodology to forecast the distribution of returns. My goal was to make it easier for people to use the model for running a business (and not just generating numbers and forecasts). In this version I took that a step further and essentially removed the events to focus just on the aggregate. And the result is something that is simpler to understand and audit and surely easier to use.
Thus, on the Operating Forecast sheet you’ll see the fund operations forecasted over a period of up to 10 years. The model uses a semi-annual time-period (H1 for first half of the year and H2 for second half of the year) to map the time period simple to use. Since the distribution of deals done can be somewhat seasonal and bunchy over smaller time periods, I chose semi-annual periods as the best blend of calculation accuracy and detail meaningfulness.
The existing investments (investments already made) are shown over time, and then below the forecasted investments are estimated over the future time period. Excluding any already-invested follow-on rounds, the follow-on capital is allocated separately from the new investments so that the timing of follow-on rounds can be allocated separately, as well as any difference in returns from follow-on v. new invested capital (see how-to part 1 for explanation).
After the fund’s investments and proceeds are estimated, the model breaks down the cash flows for the limited partners (LPs) and general partners (GPs). The model doesn’t distinguish between or calculate specific returns for invidual LPs or GPs, but that can be done easily by adding lines at the end of the model using your own breakdown of fund ownership by LP and GP.
Note: when forecasting proceeds, the model offers three different distribution timing options:
- Straight-line distribution over a harvesting period that matches the investing period
- Straight-line distribution over a harvesting period of your choice (independent of investing period)
- Triangular distribution over a harvesting period of your choice.
Instructions are on the Assumptions sheet in the model in how to use the inputs to choose your preferred proceeds distribution.
In addition to cash flow, multiples and IRR, the model also calculates a number of standard fund metrics to help gauge the fund’s performance. In addition to tracking investments and proceeds (i.e. distributions), the model calculates residual value (i.e. net asset value) of the portfolio over time and differentiates between realized and unrealized gains.
This chart on the Performance tab shows how the cash flows map out over time:
The model also calculates metrics such as Paid In Capital, Distributed to Paid in Capital, Residual Value to Paid in Capital, and Total Value to Paid In in order to provide a deeper picture of fund performance.
One of the latest additions to the model are the Fund’s financial statements. The model forecasts the income statement, statement of financial condition, statement of cash flows, and statements of partner’s capital, in order to provide the full financial outlook for the fund. In addition, there is a statement of operations (income statement) generated for the management company.
There’s a lot of detailed, complicated formulas in these sheets; my goal has been to make the model very flexible based on the different use-cases of the model and the range of assumptions that you can input, thus there are many deep formulas to account for the range of possibilities. I’ve built a number of these models for venture funds and worked to make the template applicable for as many cases as possible, but funds can have interesting structures. Contact me with any comments or questions.