5.1 - The Correlation matrix

The variance-covariance matrix was calculated successfully in the previous chapter. These numbers, as we have discussed, are too small to make sense. It is a good idea to calculate the correlation matrix before we calculate variance covariance matrix.

Let's get on with it.

How does one calculate the correlation between two stocks? You will remember the formula for correlating stocks from the previous chapter.

(image 1

Where?

Cov (xy,y) is the covariance of the two stocks

s x= Standard deviation from stock x

s y= Standard deviation stock y

This is fine for 2 stocks, but if there are 5 stocks in the portfolio we will need to use matrix operation to find the correlations. When multiple stocks are in the portfolio, then the correlations among them all will be stacked in a n x matrix (read it as an n by n matrix). If the portfolio has 5 stocks (5 being the n), then we will need to create a matrix of 5 x 5.

The formula to calculate the correlation is the same. We have already seen the variance-covariance matrix in the previous chapter. This image will be repeated for convenience.

(image 2

This will take care of the numerator portion of the formula. Now we need to calculate the denominator. This is simply the sum of stock A's standard deviation and stock B's standard deviation. If there are 5 stocks in the portfolio, we will need to calculate the denominator. This is the product of the standard deviation for all combinations of stocks within the portfolio.

Let's get started.

First, we need to calculate the standard deviations for each stock in the portfolio. Assuming you know how to do that, I will assume you have some experience. To get standard deviations, you just need to use '=Stdev()" on the daily returns array.

The same formula was used in the previous chapter. The image is -
image 3

We have stock-specific standard deviations. Now we need to find the product of each portfolio combination's standard deviation. This is done using matrix multiplication. This is easily accomplished by multiplying the standard deviation array with its transpose.

First, we create the matrix skeleton. We then highlight all cells.

image 4

We now apply the matrix multiplication function without deselecting cells. We are multiplying the standard deviation array by transposing it. You should see the image below to get an idea. Also, take a look at this formula -

image 5

As I said in the previous chapter: When you use the matrix or array function of excel, make sure to hold the "ctrl+shift+enter" combo. This is the result of creating a matrix.

image 6

Let me now paste the formula again for the correlation.

image 7

The numerator represents the variance-covariance matrix, as shown below. The denominator is the sum of the standard deviations that we just calculated.

image 8

The correlation matrix is obtained by dividing the variance-covariance matrix with the product of standard deviations. This is an element-by-element division. It is still an array function so you should use 'ctrl+shift+enter.

image 9

The resulting correlation matrix looks something like this

image 10

The correlation matrix shows us the correlation between two stocks. If I want to find the correlation between Cipla or Alkem, for example, I just need to look under the intersecting cells between Cipla, Alkem. You can do this in one of two ways:

1. Scroll down to the Alkem column by looking at the row that belongs to Cipla
2. Scroll down to the Cipla column by looking at the row belonging Alkem

Both should show the same result, i.e. 0.2285. This is obvious because the correlation between Stock A and Stock B is very similar to that of Stock A with Stock A. The matrix shows symmetrically similar values above the diagonal. In the image below, I highlighted the correlation between Cipla/Alkem and Alkem/Cipla.

image 11

The diagonal represents the stock's correlation with each other. The correlation numbers above the diagonal will be symmetrically identical to those below it.

It is obvious that the correlation between Stock A and Stock A is always 1 which is what we have highlighted in yellow boxes.

5.2 - The Portfolio Variance

Just a few more steps are required to calculate the Portfolio Variance. We need to calculate the Portfolio Variance to determine the level of risk in my portfolio. This information allows me to see clearly and I can stop driving blindly. This information can lead to many more insights. We will continue to talk about this.

To calculate portfolio variance, the first step is to assign weights for each stock. Weights simply refer to the amount of cash that we choose to invest in each stock. If I have Rs.100 and decide to invest it all in Stock A, the stock's weight is 100 percent. If I decide to invest Rs.50, Rs.20, and Rs.30 each in Stock A and B, then the weights in stock A, B and C would be respectively 50%, 20% and 30%.

I arbitrarily assigned weights for the five stocks in my portfolio

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

At this stage, there is no science behind assigning weights. This part will be discussed in greater detail at a later stage of the module.

Next, calculate the weighted average deviation. The weighted standard deviation simply refers to the stock's weight multiplied with its standard deviation. Cipla's standard deviation, for example, is 1.49%. Therefore, its weighted average deviation would be 0.1%.

These are the weights of the 5 stocks in the portfolio.
image 12

Please note that the total weight must equal 100%.. This means that the sum of all the weights in stock should equal 100%.

This stage contains all of the components required to calculate the "Portfolio Variance". Below is the formula for calculating Portfolio Variance.

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

Where?

Wt.SD stands for weights standard deviation array.

The above formula will be implemented in three steps.

1. Calculate the product between Transpose of Weight.SD and the correlation matrix. This will give you a row matrix that has 5 elements.
2. Multiply the row matrix result with the weighted standard deviation array. This will give you a single number.
3. To calculate the portfolio variance, multiply the result by the square root.

Let's move on and solve the portfolio variance problem in the same order.

I will create a row matrix called "M1" with five elements. This matrix will contain the product from the Transpose Wt.SD and the correlation matrix.

image 13

You will need to select the empty array space, and simultaneously hold the ctrl+shift+enter keys.

Now we create a second value, 'M2'. It contains M1's product and the weighted standard deviation.

image 14

The value of M2 is 0.000123542. The square root of this value, , is the portfolio variance.

image 15

The above operation returns 1.11%. is the portfolio variance for the 5 stocks.

Hee! It's so good!

This is exhausting. Let's take a break and figure out what the next chapter will look like.

To Summarize

1. The correlation matrix shows the relationship between two stocks in a portfolio
2. The correlation between stock A and stock B is the exact same as that between stock A and stock B.
3. The correlation of stock and itself is always 1.
4. The correlation between stock A and itself should be represented by the diagonals in a correlation matrix
5. The correlation matrix includes symmetrical values that are above and below the diagonals