## 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 –

- Look at the row belonging to Cipla and scroll till the Alkem column
- 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 –

- Calculate the product of Transpose of Wt.SD with correlation matrix. This will result in a row matrix with 5 elements
- Multiply the result obtained above (row matrix) with the weighted standard deviation array. This will result in a single number
- 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

**Download** the excel sheet used in this chapter.

### Key takeaways from this chapter –

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

Hello sir

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

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

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 ?

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.

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

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.

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

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

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?.

Yes, that is the risk of an overall portfolio.

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?

Glad you thought so, Ruban 🙂

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

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?

Yes, this should be ok.

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.

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

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?

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 🙂

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

Yes, please! Looking forward to it 🙂