Simulate five portfolio positions, each with a chance of not surviving the year and a value tied to the same funding market, to see the downside range. Free.
Five positions, each with a chance of not surviving the year and a value that moves with the same funding market as the others. The middle of the range is unremarkable and the bottom of it is the number an investor asks about.
Finance Advanced Monte Carlo Pro engine
After you install, this is the model to open.
What Does a Bad Year Look Like?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
The answer
- The middle year
- +3.2% median mark movement: nothing to see
- One year in ten
- -37.2% or worse
- One year in twenty
- -47.4% on $32.7M of marks
- Down more than 20%
- 24.2% of years: nearly one in four
Five positions at their last marks, $32,700,000 in total, and two separate things that can happen to each of them in a year. The still-standing flag is survival, one draw per position that comes back 1 or 0, with a higher chance of standing on the later-stage names. The value-change figure is the mark movement given that it survives, a three-point range running from almost nothing up past double, because a mark that goes wrong is written down rather than turned negative and a mark that goes right can multiply.
The ten correlation pairs at +0.45 across those value changes are the honest part: private marks do not move independently, they move with the funding market, and the year that reprices one of these companies reprices all of them. Click Run on 20,000 trials. The middle is unremarkable. The mean change is a rise of 3.2% and the median a rise of 2.5%, which is roughly the flat year the sheet starts from and is not what anybody is asking about.
The bottom is the answer. The tenth percentile is a fall of 37.2% and the fifth a fall of 47.4%, and the third output says the portfolio is down more than 20% in 24 of every 100 years (24.2%). Now delete all ten correlation pairs and run it again, because the comparison is the lesson. The mean does not move at all: it stays at a rise of 3.2%.
The fifth percentile improves from a 47.4% fall to a 35.7% fall, and the down-twenty rate falls from 24.2% to 15.3%. Correlation did nothing to the expected return and it did nearly nine points to the probability of the year you have to explain. To see it push the other way, set all ten pairs to +0.75, which is what a genuinely locked funding market looks like, and the down-twenty rate goes to 27.3% with the fifth percentile at a 53.0% fall.
Two things this cannot tell you. It is a one-year mark model, not a fund return model, so it says nothing about what these positions are eventually worth or about the timing of distributions. And the correlation applies only to the survivors, so a funding market bad enough to cause simultaneous write-offs is worse than anything in this run.
To adapt it, put your own positions and last marks in largest first, set each survival probability by stage from your own loss history, and set the correlation from how tightly your positions really share a market rather than leaving it at 0.45.
The model
It arrives on a tab called Template: Portfolio Shock, carrying these columns:
- Position
- Carrying value ($)
- Still standing at year end (1 = yes)
- Value change if it survives (multiple)
- Value at year end ($)
with the model computed beside the data:
| Portfolio | 32,700,000 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Is that margin gap real or just a wider spread?Do Our Two Service Lines Earn the Same Margin?
- How much cash does a year need before it is safe, not just funded?How much cash gives 95% odds of not running out?
- Is your price a plan, or a coin toss against the margin target?What price keeps 80% odds of hitting the margin target?
- What does the monthly transfer have to be for the goal to hold nine times in ten?What monthly saving gives 90% odds of hitting the goal on time?
- A $10,000 product launch with an uncertain price, an uncertain quantity and an uncertain unit cost. One recalculation shows one NPV and tells you nothing. Twenty thousand show a mean of $24,879, a median of $24,291 and a positive NPV in 99.6% of them.What are the odds this project clears a positive NPV?
- When does your startup actually run out of money?When Does the Startup Run Out of Cash?
Every model like this one, and the method behind them: Monte Carlo simulation.