Portfolio Optimization: Maximising Returns with Fixed Risk Levels

Portfolio Optimisation (Part 2)

Working with the Weights

The previous chapter demonstrated how Excel’s Solver tool identifies the minimum variance portfolio by adjusting stock weights until the lowest possible risk level is achieved. That exercise produced a portfolio with an annual variance of 12 per cent and an expected return of approximately 18 per cent. This chapter builds on that foundation to explore one of the most important concepts in portfolio theory: the Efficient Frontier.

The central insight driving this chapter is one introduced earlier but worth restating clearly. For any fixed level of portfolio risk, it is possible to construct multiple portfolios with different return characteristics. Some weight combinations will deliver the highest achievable return at that risk level. Others will deliver the lowest. Between those two extremes lies a spectrum of possibilities. Mapping these possibilities systematically is what ultimately produces the Efficient Frontier.

More Optimisation

The minimum variance portfolio established in the previous chapter sets the floor. No combination of weights applied to these five stocks can produce a portfolio variance below 12 per cent. Any increase in risk beyond this floor opens up new possibilities, with different weight combinations producing different maximum and minimum returns at each successive risk level.

The approach taken here is to fix the portfolio variance at a series of chosen levels, then run two separate optimisations at each level: one to find the maximum achievable return and one to find the minimum achievable return. The results are then plotted to reveal the shape of the return possibilities across the risk spectrum.

A fixed variance of 15 per cent is chosen as the starting point for this exercise. Think of it this way: if the safest possible arrangement of these five stocks carries a risk of 12 per cent, then nudging the risk up slightly to 15 per cent should unlock the possibility of earning a higher return. The question is: how much higher can that return go, and how low can it fall, at exactly this level of risk?

Step 1: Open the Solver Tool

The Solver tool is accessed through the Data ribbon in Excel. The optimised weights from the minimum variance portfolio calculated in the previous chapter are retained in the spreadsheet as a reference point. These will change as Solver identifies new optimal weight combinations for the 15 per cent risk constraint.

Step 2: Set the Objective

To find the maximum return achievable at a portfolio variance of 15 per cent, the Solver objective is set to maximise the expected portfolio return cell. This is the opposite instruction to the one given in the previous chapter, where the objective was to minimise variance.

Step 3: Identify the Variable Cells

As before, the variable cells are the five stock weight inputs. Solver is permitted to adjust any or all of these weights in pursuit of the objective, subject to the constraints defined in the next step.

Step 4: Apply the Constraints

Two constraints govern this optimisation.

The first constraint requires that the total of all five weights equals exactly 100 per cent. This ensures the full corpus remains invested.

The second constraint fixes the portfolio variance at exactly 15 per cent. This is the critical addition that distinguishes this optimisation from the previous one. Rather than allowing variance to fall to its natural minimum, it is held at a specified level so that the return possibilities at that exact risk level can be explored.

With both constraints applied and the objective set to maximise, Solver is run.

The result is striking. At a fixed risk of 15 per cent, the maximum return this portfolio can generate is approximately 28 per cent. To achieve this, Solver shifts more weight toward the stronger-performing stocks in the portfolio, reducing exposure to the slower-growing holdings.

Finding the Minimum Return at the Same Risk Level

The same process is then repeated with one change: the objective is switched from maximising to minimising the expected portfolio return, whilst keeping the 15 per cent variance constraint in place.

The result is equally revealing. At the same fixed risk of 15 per cent, the minimum return this portfolio can generate is approximately 11 per cent. The weights required to produce this outcome are arranged very differently, concentrating capital in lower-returning stocks whilst still carrying the same overall level of risk.

This side-by-side comparison makes the core principle immediately tangible. Two investors, both accepting exactly 15 per cent risk, both holding the same five stocks, but with different weight arrangements, could end up with returns as far apart as 11 per cent and 28 per cent. The difference between them is not luck or market timing. It is purely the allocation of weights.

Three Portfolios Defined

The optimisation work completed across these two chapters has now produced three distinct portfolios from the same set of five stocks.

Portfolio 1 is the minimum variance portfolio, carrying a risk of 12 per cent and an expected return of approximately 18 per cent. This is the safest possible arrangement of these five stocks.

Portfolio 2 is the maximum return portfolio at a fixed variance of 15 per cent, delivering an expected return of approximately 28 per cent. This is the best outcome available at this risk level.

Portfolio 3 is the minimum return portfolio at the same fixed variance of 15 per cent, delivering an expected return of approximately 11 per cent. This is the worst outcome available at the same risk level.

The contrast between Portfolio 2 and Portfolio 3 is the most instructive part of this exercise. Both carry identical risk. Yet one delivers more than twice the return of the other. No rational investor would choose Portfolio 3 over Portfolio 2 at this risk level. The difference is not the stocks chosen but the proportion of capital allocated to each.

The next step is to repeat this process across a range of risk levels, generating maximum and minimum return figures at each point. Plotting those data points produces the Efficient Frontier, which will be examined in the chapter that follows.