Portfolio Optimisation how to Maximise Returns and Minimise Risk

Portfolio Optimization (Part 1)

– A tale of 2 stocks

Portfolio Optimization (Part 1)

Let’s start with an example. 

What are your thoughts on overall portfolio returns from equal investments in Infosys and Biocon? The predicted return of Infosys is 22%, while Biocon forecasts 15%.

This may come across as a common MBA query, yet it is significant and you should have the ability to reply it now.

The portfolio being equally weighted between Infosys and Biocon, the expected return would consist of 50% each.

= Weight of investment in Infosys * Expected return of Infosys + Weight of investment in Biocon * Expected return of Biocon

Remember we discussed the concept of “Expected Return of a stock” in the prior chapter? Now, let us move forward and find out the answer –

50% * 22% + 50% * 15%

=11% + 7.5%

= 18.5%

This portfolio is anticipated to generate an annual return of 18.5%.

Let’s alter the weightings: what if we put 30% into Infosys and 70% in Biocon, or maybe 70% into Infosys and 30% in Biocon?

Let’s try to come up with a solution for this. Situation 1 

30% * 22% + 70% * 15%

= 6.6% + 10.5%

17.1%

Here’s the second case- 

70% * 22% + 30% * 15%

=15.4% + 4.5%

=19.9%

It is evident that we can achieve various weight combinations. Here is the list of several options –

It is clear that the investment weight has an effect on returns. For instance, investing 40% in Infy and 60% in Biocon yielded a return of 17.8%, whereas reversing those proportions delivered 19.2%, representing a difference of 2%.

This highlights the fact that, as the investment portfolio weights fluctuate, so do its returns and risk levels. It is important to keep this in mind when managing a portfolio.

What would it be like to analyze the data from a portfolio containing ‘n’ stocks and intelligently decide how much to invest in each stock, maximizing returns? It sounds amazing, right?

Optimising one’s portfolio generally implies adjusting the weights of a given set of stocks, in order to achieve the desired gains- 

To get the best possible returns, you identify the investment weights. 

To get the least possible risk, you identify investment weights 

If you’re feeling confused, don’t be. Just keep reading!

Beware! Jargon ahead! T

It’s clear why portfolio optimisation is essential, so let’s move on.

Let’s continue optimising the portfolio. There are a few terms I need you to be aware of before we proceed any further.

Minimum variance portfolio- Given a portfolio of 10 stocks, it should be evident that we can tweak the allocations to result in varying risk and return profiles. Every single set of weights creates a distinct portfolio, such as an equal-weighted one with each stock represented by 10% or another where you invest a 30% share in one stock and 7.8% in the other nine. With the number of potential combinations seemingly limitless, every combination produces its own unique risk/return potential.

Given this, a set of stock weights exists that can create the lowest possible risk portfolio. This portfolio is technically referred to as the “Minimum Variance Portfolio”, and is the best choice for investors who are highly averse to risk. It provides the least amount of risk exposure.

Maximum Return portfolio – A Maximum Return portfolio can be created with a combination of weights to achieve the highest return attainable. Unfortunately, it also means that along with this increase in reward, risk will be higher than that of a minimum variance portfolio.

Fixed variance, multiple portfolios- This concept is something you should be aware of at this stage. It may appear complex, but I’m sure as we move through this chapter (or maybe the next) and get to portfolio optimisation, you will gain a more comprehensive understanding.

For a given level of risk or variance, two distinct portfolios can be created to provide the highest and lowest possible returns.

We’ll take the risk of a portfolio being 15% as an example. Assuming this, one portfolio could give up to 30%, the highest return possible, while another yields only 12%, the lowest return achievable. In each case, though, the risk remains constant at 15%.

Between these two portfolios, there is a wide range of alternate combinations that present varying potential returns. Put simply, for a given level of risk, it is possible to find a combination with the highest return and one with an inferior performance.

We’ll discuss this concept again. 

– Portfolio optimisation (steps)

Here’s the recollection of all the portfolo and its associated weights that we have been working on in the previous few chapters.

Remember, the weights assigned were random and there was no rationale behind it. This particular portfolio with these specific weights had an estimated annual variance of 17.64% and a projected return of 55.14%.

Our aim is to optimise our portfolio in order to reach the desired results. To aid us in doing this, we need ‘Solver Tool’ located in the ‘Data ribbon’ of Excel.

Many of you may not have added the ‘solver’ tool from Excel’s add-ins, leading to it being absent from the Data ribbon. To make use of the feature, follow these straightforward steps –

To access your Files, click on the Excel sheet that has been opened.

Select Options

Select Add-ins (last but one option)

Click on ‘Solver Add Ins’

Click on “Go”

Check on “Solved Add ins” once again

Hit Ok and shut it.

If necessary, shut down the excel document and reboot your device.

Check under data ribbon – you should be able to find the solver tool

First, let us maximise our portfolio by finding the “Minimum Variance portfolio.” Here are a few steps to help you reach that goal.

Step 1 – Organise your data. This is essential for using solver, so be sure that your cells are linked and the data is arranged tidily. Do not attempt to manually enter the information into a spreadsheet; this is how it should look in Excel at this stage.

I have highlighted two relevant areas, which are vital for optimisation. The foremost section has the weights attributed to each share. It is unsurprising that these figures will be modified following portfolio optimization. The second region contains the predicted return and yearly portfolio variance calculation, which will surely alter when we optimise the portfolio.

Step 2 – Utilise the solver tool in Excel to determine optimal weights. Let’s give a brief overview of this tool: you can use solver to work with the concept of an ‘objective’, which is essentially a data point that results from a set of formulas. 

The objective’s value can be minimised, maximised or set to a desired value while changing certain variables; these variables are elements of the formulas used in computing the objective itself. Taking an example, if we want to reduce the variance of a portfolio, we can do so by adjusting the weightage assigned to each stock. Here, variance is our objective and weights are our variables.

When commanding the solver to minimize the objective (variance in this case), Excel’s solver will quickly assess the formulas used and work to reach the lowest possible value.

I’m calling on the solver tool and I’m going to ask it to minimise the variance, as seen in the image below.

When you select the data ribbon, followed by Solver, you will be presented with the tool. It is necessary to set the objective here; this being the portfolio’s annual variance – we are aiming to locate the minimum variance portfolio.

Check the below image- 

The objective is set to ‘Annual portfolio variance’, indicated by the cell address in the ‘set objective’ box. The cell containing the annual portfolio variance is further marked with a red arrow, while a green one indicates that we are minimising this value.

We can inform the solver tool that we need to reduce the objective by altering the weights given to each stock.

The “By changing variable cells” field has been emphasised in this instance, as this displays the assigned weights for each stock.

 In the “subject to constraints” field, the solver will minimize variance by altering the weights of each stock. Here, it is requesting any constraints to bear in mind when attempting to reduce the variance.”

I am confident that setting a limit of 100% for the total weight of all stocks will help to avoid any issues with the solver, since it is an excel tool, and would not appreciate missing out on investment in certain stocks.

In order to include a constraint, select ‘add’. This action will open up the following window –

Under Cell reference, I will give the total weight of stocks so that it equals 100%. Beside this, a drop down menu provides multiple options, choose ‘=’. The constraint here is also 100% – I have typed it out.

I request that the solver finds the lowest variance possible with all the stocks having a weight of 100%. This is what it currently looks like –

The configuration for the solver is now finished. This is the last page to be viewed before proceeding to press “Solve”. It looks like this –

I highlighted the starting weights for reference. After optimising, the portfolios’ weights will be changed to minimise variance. Now let’s see what Solver has in store for us, and hit ‘solve’!

Here you are!

Solver has identified the optimal ‘minimum variance’ portfolio, determining appropriate weights for each stock.

For example, Cipla’s weight should be raised from its current 7% to 29.58%, and Idea’s reduced to 5.22% from 16%. Additionally, the variance of this portfolio is projected at 15.57%, down from 17.64%. Similarly, its expected return has diminished to 36.25%, compared to 55.14% previously.

No matter which stocks you choose to invest in, the lowest risk you could be exposed to is 15.57%. So, if these 5 stocks are your only option, then that is the variance you must accept.