What is your diversification rule costing you?
Nine deals, $12M of capital and a concentration limit. The optimizer prices the diversification rule at $1.36M, which is a number worth having before the next investment committee.
Finance Intermediate Optimization free
After you install, this is the model to open.
Which Deals Can We Actually Fund?
- 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.
The answer
- The optimal set
- $34.26M deals 1, 3, 5, 6, 7 and 8, deploying exactly $12M
- Ranking by multiple
- $32.63M the instinctive list leaves $1.63M behind
- The mandate, priced
- $1.36M unconstrained, the same fund returns $35.62M
- Deal 4
- Out third-best multiple on the sheet and still not funded
Nine live deals, $12M to deploy, and one rule: no more than $4M in any sector. The expected multiple is worked out on the sheet and then deliberately left out of the model, because it is the number the room will argue from and it is not the number that decides. Click Run: the optimizer funds Deals 1, 3, 5, 6, 7 and 8, deploying exactly $12.0M for $34.26M of expected proceeds.
Deal 4 carries the third-best multiple on the sheet at 3.2 times and does not make the cut. Compare that with the two ways a partnership actually picks. Ranking by multiple and funding down the list until the money runs out returns $32.63M, and that is the plan the sheet opens on. Putting the biggest expected outcomes first instead returns $31.13M.
So the sorting instinct, whichever way you sort, costs between $1.6M and $3.1M of expected value, and it costs it silently, because each individual choice looks defensible. The reason is that the fund is not choosing deals, it is choosing a set that adds up to twelve, and the best set contains a deal that would lose a one-by-one comparison.
Now take the four sector limits out of the constraint boxes and run it again: the optimizer returns $35.62M by putting $5.4M, nearly half the fund, into two health deals. The difference between those two runs, $1.36M of expected proceeds, is the price of the mandate. That is the single most useful thing this sheet does, because a diversification rule is usually defended in principle and never costed, and $1.36M is a number a partnership can actually weigh against the risk it buys.
What the model cannot tell you is how correlated those exits are, and treating each deal's expected proceeds as one fixed number hides the fact that a seed portfolio's return is dominated by its best outcome rather than by its average one. That is a question for a simulation rather than an optimizer. To make it yours, replace the nine rows with your live pipeline, put your own underwriting cases in the Expected proceeds column, and rewrite the sector limit lines to whatever your partnership agreement actually says.
The model
It arrives on a tab called Template: Which Deals to Fund, carrying these columns:
- Sector
- Do it (1 = yes)
- Check ($M)
- Expected proceeds ($M)
- Proceeds ($M)
with the model computed beside the data:
| Capital deployed ($M) | 11.2 |
| Fintech ($M) | 1.6 |
| Health ($M) | 2.3 |
| Climate ($M) | 3.4 |
| B2B software ($M) | 3.9 |
| Expected proceeds ($M) | 32.63 |
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
- Which of your cost lines are really one risk?Which Two Costs Move Together?
- What does half a point of fee cost the investor?What Fee Does the Fund Need to Clear the Hurdle?
- What gross multiple does the hurdle actually demand?Will the Fund Clear Its Hurdle?
- What does your target return cost you in risk?The Cheapest Way to Hit the Target Return
- Which fund risks are worth paying to cover?What Could Go Wrong in the Fund?
- 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.What Does a Bad Year Look Like?
Every model like this one, and the method behind them: Optimization in Google Sheets.