9.1 – Working with the weights
In the previous chapter we introduced the concept of portfolio optimization using excel’s solver tool. We will build on the same concept in this chapter and proceed to understand an important portfolio concept, often referred to as the ‘Efficient Frontier’.
Recall in the previous chapter, we discussed how a portfolio can produce multiple return series for a fixed portfolio variance. We will now go ahead and see how this works. This concept will eventually lead us to understanding portfolio optimization better.
In the previous chapter, we optimized the portfolio to produce the minimum variance portfolio. The results, in terms of weights of individual stocks were as follows –
Sl No | Stock name | Pre optimized weight | Optimized for minimum variance |
---|---|---|---|
01 | Cipla | 7% | 29.58% |
02 | Idea | 16% | 5.22% |
03 | Wonderla | 25% | 30.22% |
04 | PVR | 30% | 16.47% |
05 | Alkem | 22% | 18.51% |
And the expected portfolio return and the portfolio variance is as follows –
Pre optimized | Optimized for minimum variance | |
---|---|---|
Expected Portfolio return | 55.14% | 36.35% |
Portfolio Variance | 17.64% | 15.57% |
Here is where things start to get a little interesting. So far what we have achieved in terms of portfolio optimization is merely a minimum variance portfolio. Like we discussed in the previous chapter, for every fixed risk level, there could be multiple unique portfolio with varying return characteristics. We will now go ahead and explore this in greater detail.
We know at 15.57% portfolio variance, the return expected is 36.35%. We will now go ahead and increase the risk maybe to 17%, and calculate the highest and lowest possible returns for this. In other words, we are essentially trying to identify the highest and lowest possible return for a fixed portfolio variance of 17%. Also, do pay attention here – when I say increase the risk, we are essentially fixing the risk to certain desired level. 17% for now.
9.2 – More optimization
The general direction we are headed is this – we know the minimum risk possible for this portfolio is 15.57%. We have also noted the return achievable at this level of risk i.e – 36.35%. Like I mentioned earlier, we will now increase the risk a notch higher and note down the maximum and minimum return for this risk. Along with the return, we will also note the investment weights. We will then increase the risk another notch higher and again note the max and min return along with the weights. We will do few such iterations and note down all the observation.
Eventually, I would like to plot a scatter plot of fixed risk along with its respective max return and min return data points and study this scatter plot in greater detail. This scatter plot will help us understand portfolio optimization.
So let us get started by fixing the risk at 17%. Please note, I’ve opted 17% just like that, it could very well have been 16% or 18%.
Step 1 – Invoke the solver
As I explained in the previous chapter, I’ve invoked the solver calculator by clicking on the data ribbon. I’ve highlighted the optimized weights for the minimum variance portfolio, this is just for your reference.
Step 2 – Set the parameters
To begin with let us find out the maximum return one can achieve for a fixed 17% risk. For this, we need to set the objective to maximize the ‘expected portfolio return’. The same is highlighted as shown below –
Step 3 – Select the weights
The next step is to ensure that we tell the solver tool that we want to optimize the portfolio for maximum return by varying the weights. This is very similar to what we did in the previous chapter.
Do note, the weights here are the variable cells.
Step 4 – Set the constraints
Now, here is the important part of the optimization where we set the constraints. We now tell solver that we need to maximize the returns @ 17% risk, by varying the investment weights. We do these while keeping the following two constraints –
- The total weights add up to 100%
- The Portfolio risk is fixed to 17%
The constraints section now looks like this –
With these constraints loaded and rest of the parameters specified, we can go ahead and click on ‘solve’ to figure out the maximum return possible @ 17%, along with the respective weights.
The result upon optimization is as follows –
The maximum possible returns @17% portfolio variance happens to be 55.87%. However, to achieve this, the weights are as show above. Notice how the weights for this portfolio has changes when compared to the minimum variance portfolio.
We will now proceed to figure out the minimum return possible for the same fixed amount of risk, which is 17% in our case. Before we proceed, here is a table that I’m compiling of all the various portfolios that we are building, along with its respective weights and risk return characteristics.
We are now working on portfolio 3 (P3), which is the minimum risk possible for a fixed risk of 17%. Here is the solver tool, fully loaded and ready to be optimized.
Notice, while other variables remain the same, the objective is shifted to minimize from maximize. Upon optimization, the return is now minimized to 18.35%. Clearly, for the same given risk, we have now established two unique portfolios with different possible return characteristics, all these while just changing the investment weights in the stocks.
Here are the three unique portfolios that we have generated so far –
Just to recall – P1 is the minimum variance portfolio, P2 max risk @17%, and P3 is min risk at 17%.
9.3 – Efficient Frontier
As discussed earlier, we can now increase the risk a notch higher to maybe 18%, 19%, and 21% and identify the maximum and minimum risk at both these risk levels. Remember, our end objective is attain a scatter plot of the risk and return profile and study its characteristics. I’ve gone ahead and optimized the portfolios for all the risk points, and at each point, I’ve identified the maximum and minimum return possible. Please note, I’ve rounded off the decimal values here, just so that the table looks pretty ☺
If you notice, I’ve highlighted the risk and return values of each portfolios. I’ll now go ahead and plot a scatter plot of these data points and see, what I can see.
To plot a scatter plot, simply select the data points and opt for the scatter plot under the insert ribbon. This is how it looks –
Once you click on the scatter plot, you will be able to see the how the plot appears. Here is how it looks, of course, I’ve tried to format the graph to make it look more presentable.
This curve that you see above my friend, is called the ‘efficient frontier’ of this portfolio. So what do we understand from this curve and why is it so important? Well, quite a few things, lets deal with it one by one –
- As you can see, the X-axis represents risk and the Y-axis represents returns
- Starting from the left most point, the one which seems to be a little isolated from the rest, represents the minimum variance portfolio. We know this portfolio has a risk of 15.57% with a return of 36.25%.
- We now move focus to 17% risk (notice the x axis), you can find two plots, one at 18.35% and another at 55.87% – what does this tell you?
- It tells us that at 17% risk (or when we are particular about fixing the risk at 17%), the best possible portfolio can achieve a return of 55.87%
- The worst possible portfolio (in terms of return) is 18.35%
- In simple terms, when you fix a level of risk you are comfortable, you should aim to maximize the return
- There are multiple other portfolios that are possible between 18.35% and 55.87% (when we fix risk at 17%) these would be represented as plots between the minimum and maximum return. All these portfolios are considered inefficient, the minimum return portfolio being the worst amongst the rest
- So as an investor, your aim should be to maximum the return, especially when you have some clarity on how much risk you are willing to bear
- You can notice the same behavior for risks at 18%, 19%m and 21%
- The best possible portfolios, or in other words, the efficient portfolio will always lie on the line above the minimum variance portfolio. This line is highlighted below
So, you as an investor, should always aim to create a portfolio, which lies on the efficient frontier, and as you may realize, creating this portfolio is merely a function of rearranging weights as per the results obtained in portfolio optimization.
Think about it – when you risk your money, you obviously want the best possible return, right? This is exactly what the curve above is trying to convey to us. Its prompting us to create portfolios more efficiently.
In the next chapter, we will take a quick look at a concept called “Value at risk” and then proceed to understanding risk from a trader’s perspective.
You can download the excel sheet used in this chapter.
Key takeaways from this chapter
- A portfolio with certain weights to each stock is considered unique
- When we fix the desired level of risk, we can optimize the portfolio to yield the minimum return and maximum return portfolios
- Between the min and max return portfolio (for a given fixed level of risk), we can have multiple unique portfolios
- The scatter plot of risk and return gives us the efficient frontier
- For a given level of risk, the best possible portfolio one can construct would lie on the efficient frontier, all other portfolios are deemed inefficient
Hi Karthik,
Thanks for another insightful chapter.
In our example, if we look at Unique Portfolio table, Cipla & Wonderela seems to major contributors of risk and Idea & Alkem appears to major contributors of Reward. PVR looks neutral.
Is there anything more we can do to it as next step, like replacing risk stocks with some other better stocks?
Thanks & regards
Santosh shetti☺
Replacing stocks is more of a stock picking skill 🙂
Portfolio optimization is more of what can be done once you’ve picked the stocks.
Hi Kartik,
All the tutorials are awesome. They reflect the trouble you have gone through in preparing them and your hard work.
Eagerly waiting for complete PDFs of module 9 and 10.(and further modules if you have planned them)
All the best!!
Thanks for the kind words, Yash 🙂
The PDF will be ready once the module is complete. My guess is that it will be ready by end of July 🙂
Dear Karthik,
I would be happy if you can brief the calculation of Min Risk say @17% since i’m not getting the correct answer. Moreover I’m using Excel 2007 version and i didn’t find the check box “Make Unconstrained Variables Non-Negative” and the selection box “Select a Solving Method GRG Nonlinear”.
Do help me out in getting the correct answer. I’m not able to attach any files since its not allowed here.
Regads,
Sonjoe Joseph.
I went through your excel sheet as well, but I was unable to figure out whats wrong. I’d suggest you restart excel and check again. I’ve had instances when solver has acted a little funny. Restarting usually works.
Well still the problem is not solved.
Hi karthik,
After completing all the modules i have become a big admirer of u. Hats of to u & ur team. Presently i am working in a psu since 6 yrs but after getting shares knowledge from ur website i am intrested in pursuing a career in share market. Is there any course which i can do without leaving my present job and get a entry in NSE or BSE.
Thank u
Arun, happy to note that you liked all the information here. I’d suggest you take a look at the CFA certification offered by the CFA Institute, USA.
1. If i pursue it from CFA,USA Will i b eligible for a job in usa after completion of level 3 exmination?
2 What if i do it from NSFM WHICH IS BEING CONDUCTED BY NSE IN INDIA?
Thank u
1) CFA is recognized in US. However, I’m not too sure about the job market there.
2) You cannot really compare NCFM and CFA.
waiting for..
“Understanding risk from a trader’s perspective” Chapter
when will it publish ?
The next chapter is on Value at risk….will take it up after this chapter.
Respected Sir,
3 out of 4 stocks in my portfolio (taking 30 days data) have been bearish for past month, hence, getting bearish Equity Curve. That’s fine. But, the Efficient Frontier graph is not like the one described above. It is forming a curve which is (somewhat) horizontal mirror image of the one described here. In fact, it resembles numeral ‘7’, or ‘>’ sign.
Is it possible to have such graphs for Efficient Frontier? If yes, why does my curve looks like ‘7’; is it due to bearish stocks? If not, what possibly would have gone wrong, please suggest?
More importantly, does the Efficient Frontier always resemble (more or less) the pattern described above for any type of portfolio, although may be little skewed, of course?
Thanks in advance!!
Regards
James
I’ve not come across such a graph, even if the entire portfolio is made up of losing stocks. Are you sure you are doing this right step by step?
Thanks for confirmation. I was thinking exactly same. Actually, I figured out that there is a technical issue in my Solver Tool (Excel 2007), giving weird data points for same set of equations. Guess, it’s time to upgrade the version, or reinstalling may work.
By the way, thanks again for your help & for simplifying such advanced topics.
Regards
James
Good luck, James!
Hello Sir,
why smallcase portfolios are not optimized? I see all portfolios are equiweighted.
You will have to check with them 🙂
Any plans to release this as a feature in kite ? Could save a lot of manual effort
Not at this stage, Sajith.
The insights provided by you is great and informative. Diving into stock market with this knowledge really helps. My sincere thanks to you and your team. Looking forward for more insights.
Glad to hear that, Pankaj. Good luck and happy learning.
if a stock has been in a downtrend for a long time .. the expected returns will be negative and in a extended bear cycle like in 2008 the whole portfolios expected return might be negative .. Is it normal ?
Plus the solver tends to assign a negative weight-age to some stock while increasing in others to get a net of 100, How do we solve this problem ?
Great job by the way . I was struggling to find such concise material on the net 🙂
You can put a constraint saying minimum investment to a stock is ‘x%’. In that way, we can ensure there is some sort of exposure to each stock that we have picked.
Karthik sir what if we select 2 to 3 companies from each sector including niftybees and gold etf also …all companies are major company in that sector. this will be good idea or ..????
Yes, since it is diversified.
like hindalco and tata steel from metal sector and tata motors and TVS from automobiles and so on…thanx
Yes.