All new models. Save $25 with code STARTNOW

Get Started with the Venture Portfolio Model

An overview of the Venture Portfolio Model, detailing how the sheets work and how to input the data about your portfolio.

The Venture Portfolio Model is built to track investments, proceeds, unrealized gains and losses, and exits, of a portfolio of investments, automatically calculating IRRs, ROIs, multiples, and all fund performance metrics. This works for investors in companies using equity or debt instruments (convertible debt as well as debt with principal and/or interest payments), crypto investments, warrants, revenue sharing, investments as limited partners in funds, accelerators, angel groups, and more.

To forecast a new portfolio, check out the Venture Investor Model ›

The model automatically calculates key performance metrics IRR, ROI, and gross and net multiples, as well as Residual Value, Paid In Capital (PIC), Distributed to Paid in Capital (DPI), Residual Value to Paid in Capital (RVPI), Total Value to Paid in Capital (TVPI) over the fund’s lifetime.

How it works

The model uses a detailed system for tracking the performance of investments with a categorization system that allows for you to analyze the portfolio’s results by cohorts and categories for your investments. It works for different structures for investments and returns - equity, debt, warrants, convertibles, revenue sharing, and tracks unrealized gains as well as realized events. Optionally, you can add in forecasted events - new investments, up rounds, exits, etc. - to create a forecast at a very granular level.

The best way to start using the model is to first setup the tracking system on the Get Started sheet, then enter in the events - investments, proceeds, unrealized gains and losses, and writeoffs - into the Investments and Proceeds sheet.

Get Started

The Get Started sheet is where you setup the terms, dates, and categories to use in the model. Here’s an overview of the inputs and how they are used.

Venture Portfolio Model settings

  • What is your currency? - Model works for any currency, as there are no currency-specific calculations in the model. This label is used in some presentation areas.
  • What is the first date you want to forecast? - First date in the model; this uses the min from the deals input, but feel free to override this if you want to start the model earlier. The model runs 15 years (by quarters), and will start with this start date.
  • What is the Fund Size? - By default, this is calculated using the total investments to date. But adjust this to fit how much capital will invest over time if you have a target amount greater than this.
  • What date do you want to use for reporting? - The Performance and Investment Summary sheets report overall metrics based on this date. By default, it uses today’s date, but you can override this here if you are inputting future events and you want to report forecasted metrics. The performance metrics are completely dynamic based on the date entered here.

The categories aren’t necessary to use, but are useful for tracking the performance of subsets of the portfolio. They are also dynamic: while I have defined them in the way below, you are free to change the labels to fit how you want to track your portfolio. You can also add in rows for each of the categories to add in more options: just insert a row, type in the new option, and it will be available in the respective dropdown on the Investments and Proceeds section. 1

Investments and Proceeds

This section is used to define the cash flows and events, whether this is an investment (cash out) or a proceeds (exit), cash in.

  • Investments - Cash out to investments. This will get broken out into the categories defined below.
  • Conversion - Conversion from one type of investment to another. Like a “transfer” in quickbooks.
  • Realized loss, i.e. writeoff to zero
  • Unrealized gains or losses. This is the incremental increase (decrease) in market value on the investment recognized at that time.
  • Proceeds received from investments. This is primarily used to record proceeds from exits. You can change the label, but it has to represent a cash inflow.
  • Proceeds Option 2 - This is optional, and can change the label, but allows you to define two extra, different kinds of proceeds for your own tracking.
  • Proceeds Option 3 - This is optional, and can change the label, but allows you to define two extra, different kinds of proceeds for your own tracking.

Venture Portfolio Model settings

New / Follow

Used to define initial checks v. follow-on checks.

Investment Area

Additional category layer, can be used to denote different areas, or direct / syndicate, or a geography, etc. Feel free to not use this, or to change it to a set of categories that works for you. 2

Categories

Used to define rounds, geographies, or a meaningful categorization for the stages where capital is deployed. Feel free to not use this, or to change it to a set of categories that works for you. 2

Investment Type

Used to define and track the types of investments. This is used in the model to handle some calculations differently based on the logic behind each investment type.

Cohorts

The Cohorts section allows you to define which cohort you want investments, proceeds and gains to be allocated to, so that you can track different cohorts over time. You have the flexibility to define cohorts as cohorts of capital invested, or cohorts of companies when invested (allocating follow-on to the original initial check cohort), or other definitions as you wish. Simply enter in the dates on the cohort column to reflect the cohort you want to track each event to, and the model will automatically create the cohort metrics based on your inputs.

Investments and Proceeds

This is where you input all the investments, proceeds, unrealized gains, writeoffs, and more. It’s flexible to handle anywhere from a couple to thousands of events, and if desired it can be extended to track additional categories, carry, and other impacts not prebuilt into the model.

Venture Portfolio Model Investments and Proceeds

Knowing how to use the input structure is important to get the model reporting correctly; the model is distributed with a set of example events already in the Investments and Proceeds sheet, and the video below helps explain the structure and why it works the way it does.

Cash Flows and Investment Summaries

The Cash Flows sheet create the core calculations for the model, taking the data from the Investment and Proceeds sheet and aggregating it into quarters, overall portfolio, individual companies, and each of the categories set in the Get Started. There are no inputs on this sheet, except for the inputs around fund cash flows if you wish to track management fees, capital calls, and other expenses that are incurred outside of the investment activity. The cash flows sheet shows all the results over all of the time periods of the events in the model.

Venture Portfolio Model Cash Flows

The Investment Summaries sheet pulls from the Cash Flows sheet to report the metrics - total investments, total proceeds, IRR, gross multiple, ROI, and more - for the portfolio, each category, and each company, as of the reporting date set in Get Started.

Venture Portfolio Model Investment Summariies

Performance

The Performance sheet displays a few key portfolio performance metrics and creates a chart that shows investments, proceeds, net cash flow, cumulative investments, cumulative proceeds, and residual value over time. The chart automatically adjusts to the date ranges in the model.

Venture Portfolio Model Performance graph


  1. To see the results of this new option in the Cash Flows and Investment Summaries sheets you will have to also add in rows on those sheets, but adding them in just involves adding in the row and copying the formulas from an adjacent option. 

  2. If you are not going to use these sections, I wouldn’t delete them - at least in the beginning - because deleting them would require a number of changes to clean up each sheet where they are used for reporting. If you are sure you are not going to use them, then the right time to delete them is after you have the base reporting complete and you’ve explored the tracking and reporting options fully. 

Was this helpful? Yes / No

Thanks, I appreciate the feedback.