Module 9 Risk Management & Trading Psychology

Chapter 5

# Risk (Part 4) – Correlation Matrix & Portfolio Variance

## 5.1 – Correlation Matrix

In the previous chapter, we successfully calculated the variance co variance matrix. As we discussed, these numbers are too small for us to make any sense. Hence, as a practice, it always makes sense to calculate the correlation matrix when we calculate the variance covariance matrix.

So let us go ahead and do this.

How is the correlation between two stocks calculated? Well, hopefully from the previous chapter, you will recall the formula for correlation – Where,

Cov (x,y) is the covariance between the two stocks

σx = Standard deviation of stock x

σy = Standard deviation of stock y

This works fine if we have 2 stocks in the portfolio, but since we have 5 stocks in the portfolio, we need to resort to matrix operation to find correlations. So, when we have multiple stocks in the portfolio, the correlations between stocks are all stacked up in a n x n (read it as n by n) matrix. For example, if it is a 5 stock portfolio (5 being the n here), then we need to create a 5 x 5 matrix.

The formula for calculating the correlation remains the same. Recall, from the previous chapter, we have the variance-covariance matrix. For the sake of convenience, I’ll paste the image again here – This takes care of the numerator part of the forumal. We need to now calculat the denominatr, which is simply the product of the standart devaiation of stock A with the standard deviation of stock B. If the portfolio has 5 stock, then we need the product of the standard deviation of all possible combiation between the stocks in the portfolio.

Lets go ahead and set this up.

We first need to calculate the standard deviations of each of the stocks in the portfolio. I’m assuming you are familiar how to do this. You just need to use the ‘=Stdev()’ function on the daily returns array to get the standard deviations.

I’ve caclualted the same on excel used in the previous chapter. Here is the image – Given that we have the stock specific standard deviations, we now need to get the product of the standard deviation of all possible portfolio combination. We resort to matrix multiplication for this. This can be easily achieved by mutipliy the  standard devation array with the transpose of itself.

We first create the matrix skeleton and keep all the cells highlighted – Now, without deselecting the cells, we apply the matrix multiplication function. Note, we are multiplying the standard deviation array with the transpose of itself. The image below should give you an idea, do look at the formula used – As I mentioned in the previous chpater, whenever you use matrix or array function in excel, always hold the ‘ctrl+shit+enter’ combo. The resulting matrix looks like this – At this point let me paste the formula for the correlation again – The numerator is the variance covarince matrix as seen below, and the denominator is the product of the standard deviations which we have just calculated above – Dividing the variance covarince matrix by the product of the standard deviations should result in the correlation matrix. Do note, this is an element by element division, which is still and array function, so the use of ‘ctrl+shit+enter’ is necessary. The resulting correlation matrix looks like this – The correlation matrix gives us the correlation between any two stocks. For example, if I have to know the correlation between Cipla and Alkem, I simply have to look under the intersecting cell between Cipla and Alkem. There are two ways you can do this –

1. Look at the row belonging to Cipla and scroll till the Alkem column
2. Look at the row belonging to Alkem and scroll till the Cipla column

Both these should reflect the same result i.e 0.2285. This is quite obvious since correlation between stock A with Stock B is similar to the correlation of Stock B with Stock A. For this reason, the matrix displays simitrically similar values above and below the diagnol. Check this image below, I have highlighted the correlation between Cipla and Alkem and Alkem and Cipla – The correlations along the diagonal represents the correlation of certain stock with itself. Do note, the correlation numbers above the diagonal is symetrically similar to the correation numbers below the diagonal.

Needless to say, correlation of Stock A with Stock A is always 1, which is what we have got in the diaginal and the same is highlighed in yellow boxes.

## 5.2 – Portfolio Variance

We are just few steps away from calculating the Portfolio Variance. As I have discussed earlier, we need the portfolio variance to identify the extent of risk my portfolio is exposed to. With this information, I’m no longer driving blind. In fact, one can develop many other insights based on this. Of course, we will talk about this going forward.

The first step in calculating portfolio variance is to assign weights to the stocks. Weights are simply the amount of cash we decide to invest in each stock. For example if I have Rs.100, and I decide to invest all of that money in Stock A, then the weight in stock A is 100%. Likewise, if I decide to invest Rs.50 in A, Rs.20 in B, and Rs.30 in C, the weights in A,B, and C would be 50%, 20% , and 30% respectively.

I have arbitrarily assigned weights to the 5 stocks in the portfolio –

• Cipla @ 7%
• Idea @ 16%
• Wonderla @ 25%
• PVR @ 30%
• Alkem @ 22%

Clearly, there is no science to assigning weights at this stage. However, at a later point in the module I will discuss more about this part.

The next step is to calculate the weighted standard deviation. The Weighted standard deviation is simply the weight of a stock multiplied by its respective standard deviation. For example, Cipla’s standard deviation is 1.49%, hence its weighted standard deviation would be 7% * 1.49% = 0.10%

Here are the weights and the weighted standard deviation of 5 stocks in the portfolio – Do note, the total weight should add up to 100% i.e the sum of the individual weights in stocks should add up to 100%.

At this stage, we have all the individual components needed to calculate the ‘Portfolio Variance’. The formula to calculate the Portfolio Variance is as shown below –

Portfolio Variance = Sqrt (Transpose (Wt.SD) * Correlation Matrix * Wt. SD)

Where,

Wt.SD is the weights standard deviation array.

We will implement the above formula in 3 steps –

1. Calculate the product of Transpose of Wt.SD with correlation matrix. This will result in a row matrix with 5 elements
2. Multiply the result obtained above (row matrix) with the weighted standard deviation array. This will result in a single number
3. Take the square root of the result obtained above to get the portfolio variance

So, let’s jump straight ahead and solve for portfolio variance in the same order –

I will create row matrix called ‘M1’ with 5 elements. This will contain the product of the Transpose of Wt.SD with correlation matrix. Do note, you will have to select the empty array space and hold down the ctrl+shift+enter keys simultaneously.

We now create another value called ‘M2’, which contains the product of M1 and weighted standard deviation – We obtain the value of M2 as 0.000123542, the square root of this value is the portfolio variance.  The result for the above operation yields a value of 1.11%, which is the portfolio variance of the 5 stocks portfolio.

Phew!!

I need a break at this. Let’s figure out the next steps in the next chapter J

### Key takeaways from this chapter –

1. Correlation matrix gives out the correlation between any two stocks in a portfolio
2. Correlation between stock A with stock B is the same as the correlation between stock B with stock A
3. Correlation of a stock with itself is always 1
4. The diagonals of a correlation matrix should represent the correlation of stock A with itself
5. The correlation matrix contains symmetrical values above and below the diagonals
Module 9

#### Chapters

1. Ankit says:

Hello sir
In next module would you teach us about algo trading. This just a guess by name “Trading strategies and systems”

• Karthik Rangappa says:

As on now yes, but there is a lot of demand for a personal finance module as well 🙂

2. Shashidhar B says:

Thank you for the detailed steps , I have few questions
1. How do we add new stock to already existing portfolio , for example a stock from IPO since it doesn’t have enough data points
2. Do we wait for enough data points to calculate ? is there a minimum no of data points we should have for calculation ?
3. Can portfolio variance be used to decide to buy or sell a stock , and decide on the weight-age allocation of stock ?

• Karthik Rangappa says:

1) That would be tough. We need enough data points to do this
2) No minimum as such, I personally prefer at least 6 months of data
3) Yes you can. Watch out for chapter 7 for more details on this.

3. slah04 says:

Karthik

Now that we have calculated the Portfolio Variance to be 1.11%, what do we understand from the same? Is it good or bad? What should be an ideal variance figure?

Thanks

• Karthik Rangappa says:

There is nothing like an ideal variance figure. However, you need to watch out for higher numbers like 2.5-3%. Estimating variance gives you a sense of the risk associated with it.

4. Karthik Gurusamy says:

Hi Karthik,

Thanks a lot for all your great articles. It’s simple and very elegant. I have a slight clarification in the portfolio variance formula. Since there is Sqrt, shouldn’t it be portfolio standard deviation instead of portfolio variance.

Thanks

• Karthik Rangappa says:

Interesting, I use both Portfolio variance and portfolio SD interchangeably. But yes, what you’ve said is technically correct.

5. Pratik Modi says:

Hi,
Can you please explain what is the meaning of Portfolio Variance value=1.11%? What does 1.11% indicate here? Does it mean that total portfolio value can move 1.11% up or down when the market goes up or down?.

• Karthik Rangappa says:

Yes, that is the risk of an overall portfolio.

6. Ruban says:

Hi Karthik,

Thanks a lot for your explanation in detail. You have made the difficult subject of statistics very easy.

While calculating the correlation:

“The correlations along the diagonal represents the correlation of certain stock with itself ”

In your example, it is showing as “1”. However, when I do it, I am getting 0.99 and 0.98 values. Not exactly 1. Is it fine?

• Karthik Rangappa says:

Glad you thought so, Ruban 🙂

The correlation of a stock with itself is 1, but 0.9999 is also an acceptable value 🙂

• Ruban says:

Thanks Karthik,
I am getting the values not as ‘0.9999’ but as :
0.9861562
0.9906857
0.9863188
0.9940448
0.9865632

Is this fine too?

• Karthik Rangappa says:

Yes, this should be ok.

7. Vivek Anand says:

Hi Karthik – How do we account for dividend / bonus share in the excel sheet while calculating daily return. For Ex. BPCL gave 1:2 bonus which resulted in price falling from 683.70 to 485.55 on 14th July. Should we take this (485.55) price / adjust 13th July price or just ignore this data as one day noise?

Would assume removing one day will not really affect the average but, need your guidance before doing the same.

• Karthik Rangappa says:

Yes, you always take the price adjusted for corporate actions. Else, the data will be skewed.

8. Vivek Anand says:

Many thanks and much appreciated your response. With the risk of not bugging you would like to ask two more question for the time being

1) If, I use daily adjusted closed price as published on yahoo finance it should be ok?
2) The portfolio variance (for my portfolio) calculated by Equity curve comes to 0.99545% however, the one calculated by the variance / co-variance & correlation method comes to 0.95332% for same set of data. Is it possible?

• Karthik Rangappa says:

1) Yes, last time when I was using Yahoo, I remember they were putting up clean data. Not sure if they still do that
2) Possible. I always take the EQ curve one – helps me a quick and dirty estimate and I’m absolutely fine with it 🙂

• Vivek Anand says:

Thank you very much sir… You guys are doing a great job. Will bother you when I read the next module 🙂

• Karthik Rangappa says:

Yes, please! Looking forward to it 🙂

9. Hai Ninh says:

Dear K,

If portfolio variance is 1.11, means that portfolio Standard deviation is 10.54%? is this daily STDV of a portfolio???

• Karthik Rangappa says:

It means the standard deviation is 1.11%…which is the measure of volatility. Yes, in this case, it is for the entire portfolio and not just a single stock.

• Hai Ninh says:

Many thanks

• Karthik Rangappa says:

Welcome!

10. Hai Ninh says:

Do you have any part talking about correlation between portfolio and benchmark?

• Karthik Rangappa says:

I’m not sure if I’ve touched that topic, but you can run a correlation between the portfolio’s equity curve and the desired benchmark. This will help you get started.

11. Niteesh sharma says:

Dear Karthik,

I appriciate your effort for providing such a wonderful material on Risk Management. However, there is a thing I would like to clarify from you. As mentioned in the chapter, calculation of portfolio variance can be calculated as,

“We will implement the above formula in 3 steps –

1- Calculate the product of Transpose of Wt.SD with correlation matrix. This will result in a row matrix with 5 elements
2- Multiply the result obtained above (row matrix) with the weighted standard deviation array. This will result in a single number
3- Take the square root of the result obtained above to get the portfolio variance”

As per my understanding, #Step 2 should give me the Portfolio variance. If I use the step 3, it will give Standard Deviation of Portfolio.

With Regards,
Niteesh Sharma

• Karthik Rangappa says:

Yes, that’s absolutely correct, Niteesh.

12. Jim says:

Why is portfolio variance a square root of the matrix multiplication? the matrix multiplication should yield a result in the unit of variance, so a square root of that result should yield portfolio standard deviation. Did I miss something?

Portfolio Variance = Sqrt (Transpose (Wt.SD) * Correlation Matrix * Wt. SD)

Thanks for the detailed walkthrough!

• Karthik Rangappa says:

Yeah, maybe I should have specified –

Portfolio Standard Deviation = Sqrt (Transpose (Wt.SD) * Correlation Matrix * Wt. SD)

Thanks for point out 🙂

13. Fred says:

The excel file is wrong. Correlation for same asset should be exactly 1 (even not 0.99999)

Errors:
Average returns
number of data (N=126, not 127)
number of data for variance – co-variance should be N-1 N-1 = 125 to be used to compute variance – co-variance.

Then, correlation matrix will be correct and will give exactly 1 on the diagonal.

My friend, check your results before publishing.

• Karthik Rangappa says:

Thanks for pointing that out, Fred. Will go through the sheet again.

14. Simranjeet Singh says:

Dear Karthik Sir,
I have been repeatedly trying following all steps very carefully, but unable to get “1” value along the diagonal of correlation matrix. Kindly help. what do u think, might be the possible issue with this

• Karthik Rangappa says:

Simranjeet, it would be hard to figure out what is going wrong with your excel as there are multiple steps involved. I’d suggest you retract it or best try rebuilding (if time permits).

• Simranjeet Singh says:

Just figured it out man . It was a very fundamental problem. Kindly tell me weather “MMULT(A,B)” is equal to “MMULT(B,A)” or not. U must be laughing but i was afraid of matrix algebra in school. Anyway Cheers..

15. Alyn says:

Hi, I’m trying this with a portfolio of over 100 holdings. My correlation matrix is only yielding perfect correlation for the first security, all others are in the ranges of 98%-103%. I have a few outliers, like 106% and 183%. I’ve followed the guide the best I can and have tried recreating many times. Any idea why this is the case?

• Karthik Rangappa says:

How are you doing this, Alyn? Is it on excel?
One of the best checks for the correlation matrix is to see the values at the diagonal. If its near to 1, then probably you are on the right track.

16. Rajat says:

how to find portfolio variance if stock invested at different period for eg invested in stock A on 1st april and invested in B on 28th april.

• Karthik Rangappa says:

It would still be the same procedure.

17. Rajat says:

Sir if I have invested in stock at different period for eg. invested in stock A on 24thdec 2019 in stock B on 1st jan 19, Stock C on 20th feb 19 and stock D and E on 31st march 19.Then to find portfolio variance how many data points should I considered.Should I considered from the date of investment of first stock A or past 1 year data?

• Karthik Rangappa says:

Yes Rajat, the idea is to have the data for the stock for at least 1 year.

• Rajat says:

So Sir inorder to find out portfolio variance that I have invested from 24th dec 2018 onwards I have to consider data of past 1 year from the date of first investment in stock that is on 24th dec 2018.

• Karthik Rangappa says:

Yeah, also ensure you have the data for the same period for all the stocks.

15 chapters

21 chapters

16 chapters

12 chapters

23 chapters

13 chapters

7 chapters

19 chapters

16 chapters