What does your target return cost you in risk?

Put in the return, risk and correlation of each sleeve and get back the lowest-risk mix that hits the target. Five sleeves, a correlation grid and a target return. The optimizer reaches 6.5 percent for 8.0 percent of volatility, and the concentration cap is the only thing stopping it going a lot lower.

Words on this sheet

Weighting: How much this item counts against the others.
Correlation: How closely two things move together, on a scale from -1 to +1, where 0 is no relationship at all.
Covariance: Whether two things move together, before it is scaled into a correlation.
Sample variance: Spread, squared: the standard deviation multiplied by itself.

Finance Intermediate Optimization free

Install free How optimization works here

After you install, this is the model to open.

The Cheapest Way to Hit the Target Return

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

Cheapest 6.5% return: 8.01% volatility, target exactly met
A fifth in each sleeve: 5.48% return at 6.84% volatility: calm, and short
Private credit: 40% straight to its cap, and the cap binds
What the cap costs: 0.67 points: uncapped, the same return at 7.34%

Five sleeves, and the only thing the optimizer sets is four of the five weights. Private credit takes whatever is left, which keeps the book fully invested without anyone having to write an adding-up constraint, and the two constraints on it hold that leftover between nothing and the same 40 percent cap the others carry. Everything to the right of the sleeves is machinery: the correlation grid you type in, the covariance grid built from it, and a grid of every pair of weights, so that the portfolio variance is the honest double sum rather than a weighted average of volatilities, which is the mistake this template exists to avoid.

The sheet opens on the naive answer, a fifth in each sleeve: 5.48 percent expected return at 6.84 percent volatility, comfortable and short of the target. Click Run and the optimizer delivers exactly 6.50 percent at 8.01 percent volatility, on weights near 24 percent equities, 24 bonds, 8 property, 4 cash and 40 private credit. Two things in that answer are worth stopping on.

Private credit goes straight to its cap and stays there, which is the model telling you that the cap, not the arithmetic, is what limits this portfolio. Delete the cap on the leftover sleeve, put the four weights back to a fifth each and run again: the same 6.50 percent return comes back at 7.34 percent volatility, with more than 60 percent of the book in private credit.

The concentration rule is costing about 0.67 points of volatility, which is probably the first time anyone has put a number on it. The second thing is what the target itself costs. Raise the required return to 6.76 percent and run again from the answer you already have: volatility goes to 8.71 percent. Now type in the mix an investment committee usually proposes, 40 percent equities, 20 bonds, 10 property and nothing in cash, and read the two summary lines without running anything: the same 6.76 percent return, at 9.49 percent volatility.

That proposal is carrying about 0.78 points of volatility it is not being paid for. One caution about how you run this, because it matters more here than on the linear models. The Nonlinear method searches out from wherever the weights currently sit, so a poor starting point gets a poorer answer: start it on that committee mix and it settles at 8.81 percent instead of 8.01 and never finds its way out.

Put the weights somewhere neutral before you run, and when two runs from two different starts land close together you can believe the answer. The bigger caution is the one every mean-variance model carries: the answer is far more sensitive to the expected returns you typed than to the correlations, so change the equity return from 7.6 to 8.6 percent and watch the mix move a long way on an assumption nobody can verify.

Put your own sleeves, assumptions and correlation grid in, set your target in the expected return constraint, and treat the result as an argument rather than an instruction.

The model

It arrives on a tab called Template: Least Risk for the Target Return, carrying these columns:

Weight
Expected return (%)
Volatility (%)
Private credit (correlation)

with the model computed beside the data:

Private credit (correlation)	0.2
Global equities (correlation)	0.0256
Corporate bonds (correlation)	0.00132
Listed property (correlation)	0.0129
Cash	0
Private credit (correlation)	0.00756
Weight products	0.04
Weights add to (%)	1
Expected return of the portfolio (%)	0.0548
Portfolio variance (% squared)	0.004677
Portfolio volatility (%)	0.06839

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

Every model like this one, and the method behind them: Optimization in Google Sheets.

Install free All 379 templates