Portfolio Optimization (Part 2)
– Working with the weights
In the previous chapter, we discussed portfolio optimization with Excel’s Solver tool. Building on this idea, we will now explore an essential investment concept known as the Efficient Frontier.
In the previous chapter, we delved into how a portfolio can generate various return sequences for a fixed portfolio variance. Now, let’s delve into how this works; it will ultimately guide us towards a heightened understanding of portfolio optimisation
In the previous chapter, our objective was to devise a portfolio with minimum variance and the resulting weights of each stock were, as follows –
Here is where things start to become more fascinating. We’ve already constructed a portfolio through optimising for variance. As mentioned in the previous chapter, each risk level could compose varied portfolios with different return characteristics. Now, let’s examine this idea in greater detail.
At 15.57% portfolio variance, an expected return of 36.35% was identified. To further explore the risk associated with this, the variance is to be increased to 17%, allowing us to assess the highest and lowest possible returns for that level of risk. So, essentially we are looking at defining the highest and lowest returns possible at a fixed portfolio variance of 17%. Please note: when I reference increasing risk, it simply means fixing the risk to a desired amount – 17%.
– More optimisation
We understand that the minimum risk associated with this portfolio is 15.57%, which offers a return of 36.35%. We will now increase the risk incrementally, noting down the maximum and minimum returns and corresponding investment weights for each level of risk after every iteration.
I ultimately want to create a scatter plot of fixed risk data points and their corresponding max return and min returns, to analyze this visualization further. This should provide us with insight into portfolio optimization.
Let us begin by settling on a risk level of 17%. I chose that figure arbitrarily; it could have easily been 16% or 18%.
Step 1 – Invoke the solver
As previously stated, I’ve accessed the solver calculator through the data ribbon. The minimum variance portfolio’s optimised weights have been highlighted for your reference.
Step 2 – Set the parameters
We should set the objective to maximise expected portfolio return in order to determine the highest yield that can be obtained with a 17% degree of risk. The following displays this point:
Step 3 – Select the weights
To ensure the portfolio is optimised for maximum return, next we will tell the solver tool to vary its weights. This mirrors what was done in the preceding chapter.
Take note that the weights here are in the variable cells.
Step 4 – Set the constraints
Here is the essential part of optimization, where we set the restrictions. We must maximize returns @ 17% risk by changing the investment proportions – all while abiding by two constrictions.
Altogether, the total sum of all weights is equal to 100%.
The Portfolio’s risk is set at 17%.
The parameters of the constraints look like this-
Having this information set and all other parameters specified, we can click ‘solve’ to determine the maximum return at 17% and the associated weights.
The outcome after optimization is this –
The maximum possible returns of this portfolio is set at 55.87% and a variance of 17%. To reach this, the weights need to be adjusted, differing from those of the minimum variance portfolio.
Let us now move ahead and determine the minimum yield achievable for the same amount of risk, which is 17%. Here is a document that I have compiled with all the different portfolios we are constructing, together with their weights and risk-return characteristics.
We are currently utilising portfolio 3 (P3), with a fixed risk of 17%. Here is the solver tool, armed and ready for optimisation.
Noting that other conditions have remained constant, the aim has been changed from maximizing to minimizing returns. After optimization, the return is now 18.35%. Thus, simply by adjusting the weights of stocks, two completely different portfolios with numerous probable return characteristics can be created, while preserving the identical level of risk
The 3 unique portfolios generated so far are-
Remember, P1 is the portfolio with minimum variance, P2 has the most risk at 17%, and P3 has the least risk at 17%.
– Efficient Frontier
As mentioned before, we can raise the risk levels to 18%, 19%, and 21%. Our goal is to create a graph of risk and return and examine its features. I have already gone ahead and developed the portfolios for all these risk points, with their highest/lowest returns rounded off for visual appeal.
I’ve highlighted the risk and return values of each portfolio. Now I’m plotting these points on a scatter graph, to identify any patterns.
To plot a scatter graph, simply select the data and then choose the scatter plot option within the ‘Insert’ tab. The result looks like this –
When you click the scatter plot, you will get a glimpse of its appearance. I have formatted the graph to make it more presentable.
The curve above my companion is known as the ‘efficient frontier’ of this portfolio. Consequently, what does it tell us and why is it so significant? Quite a few things can be discovered from this, let’s address them one by one –
On this graph, you can observe that the X-axis denotes risk and the Y-axis shows returns.
Beginning with the leftmost point, which appears to be somewhat separate from the rest, this is the minimum variance portfolio. This portfolio has a risk level of 15.57%, with a return of 36.25%.
Now looking at the x axis with 17% risk, there are two plots, one at 18.35%, and the other at 55.87%. This data illustrates what?
At a risk of 17%, the most profitable portfolio one can obtain is 55.87%.
The poorest possible portfolio return amount is 18.35%.
In other words, you should aim to maximise returns when you fix a level of risk you are comfortable with.
There are a range of portfolios that can exist between the 18.35% and 55.87% returns (with a risk level of 17%). These will appear as plots between the lowest and highest possible results. These portfolios have been deemed inefficient, with the lowest return portfolio being particularly poor in comparison to the others.
As an investor, your goal should be to maximize gains while understanding the amount of risk you are willing to take.
It can be observed that the same trends manifest at 18%, 19%, and 21% with regard to risks.
A more efficient portfolio than the minimum variance one will always be found on the line above it, as shown below.
As an investor, always strive to construct a portfolio that sits along the efficient frontier. This can be done by adjusting the weights depending on the output of portfolio optimization.
Take into consideration that when you invest your finances, you naturally desire the highest possible gain; this is what the line above endeavours to show us. It is inspiring us to make portfolios more adequately.