Correlation Matrix and Portfolio Variance Calculation for Risk Management

Correlation Matrix

In the preceding chapter, we worked out the variance-covariance matrix; however, these figures are too slight to be of any use. To that end, when calculating the variance-covariance matrix, it is essential to also calculate its correlation matrix.

Let’s proceed and get it done.

Hopefully, you will remember the formula for correlation from the previous chapter and how to calculate it between two stocks.

Where,

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

σx = Standard deviation of stock x

σy = Standard deviation of stock y

Having more than two stocks in the portfolio necessitates a matrix operation to determine correlations between them. A n x n (read it as n by n) matrix must be created, where ‘n’ is equal to the number of stocks in the portfolio. For example, if there are five stocks, then a 5 x 5 matrix must be formed.

The formula to calculate the correlation remains consistent. Remember, we had a variance-covariance matrix in the preceding chapter. To make it simpler, I’m attaching the picture –

Now that we have taken care of the numerator, we move on to calculating the denominator. It is simply the multiplication of standard deviations for each stock included in the portfolio; if there are 5 stocks, then this will entail finding the product of standard deviations for all possible combinations.

Let’s move forward and get this organised.

We must begin by calculating the standard deviations of each of the stocks in the portfolio. It is assumed you are familiar with the process; you can use the ‘=Stdev()’ function on the daily returns array to obtain them.

I have employed the same Excel formula for my computations as was used in the preceding section. This is the picture I’ve got

Now that we have the stock-specific standard deviations, matrix multiplication should be used to calculate the product of all possible portfolio combinations. This can be done by multiplying the standard deviation array with its own transpose.

We initially create the matrix skeleton and retain all the cells highlighted.

Without deselecting the cells, we can perform matrix multiplication. The picture below should help give you an understanding of the formula employed; it involves multiplying the standard deviation array with the transpose of itself.

Whenever you use matrix or array functions in Excel, always press ‘ctrl+shift+enter’. This will result in a matrix such as the example illustrated.

At this juncture, allow me to reiterate the formula for correlation.

The numerator is the variance-covariance matrix, while the denominator is the product of the standard deviations worked out earlier.

The variance-covariance matrix can be converted into a correlation matrix by dividing it element by element with the product of their standard deviations. Remember to use ‘ctrl+shift+enter’ when doing array functions.

This matrix shows a correlation:

The correlation matrix offers us a way to find out the correlation between any two stocks. For instance, one can easily identify the connection between Cipla and Alkem by looking at the intersecting cell in the matrix. There are two alternative methods of accomplishing this task –

Examine Cipla’s row, and scroll until you reach the Alkem column.

Examine Alkem’s row and move down to the Cipla entry.

The correlation between Stock A and Stock B is mirrored by the correlation of Stock B with Stock A. As a result, the matrix shows symmetrical values in both above and below the diagonal, which should all amount to 0.2285. See the image provided for an example- I’ve highlighted Cipla and Alkem’s correlation, as well as Alkem and Cipla’s

The correlations along the diagonal show the correlation of a particular stock with itself. Note that the figures above and below the diagonal are mirror images of each other.

No doubt, the correlation for Stock A with itself is 1, as indicated on the diagonal and marked with yellow boxes.

– Portfolio Variance

We are nearly done calculating the Portfolio Variance. As I mentioned before, this will enable us to determine how much risk is associated with my portfolio. Knowing this helps avoid flying blind. Plus, there are many other potential applications we can explore in the future.

The first step in calculating portfolio variance is to assign weights to the stocks. For instance, if I have Rs.100 and decide to invest Rs.50 in Stock A, Rs.20 in Stock B and Rs.30 in Stock C, the weights for those stocks would be 50%, 20% and 30%, respectively.

I have randomly assigned weights to the five stocks in the portfolio.

Cipla @ 7%

Idea @ 16%

Wonderla @ 25%

PVR @ 30%

Alkem @ 22%

At this stage, there is no scientific method for assigning weights. Later on in the module, I will go into greater detail concerning this part.

To calculate the weighted standard deviation, the weight of a particular stock needs to be multiplied by its standard deviation. For example, Cipla has a standard deviation of 1.49%. Therefore, its weighted standard deviation would come out to be 7%*1.49%, which is 0.10%.

The following are the weights and their corresponding weighted standard deviations for the stocks in this portfolio:

It is important to remember that the entire weight of your assets should equal 100%. The individual assets’ weights combined should be 100%.

At the moment, we have secured all the single pieces needed to work out the ‘Portfolio Variance’. The procedure for determining this figure is the same as shown:

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

Where,

The Wt.SD array contains weight standard deviations.

We will implement the above formula in 3 steps –

Calculate the product of the Transpose of Wt.SD and the correlation matrix; this will yield a row matrix with 5 elements.

By multiplying the row matrix with the weighted standard deviation array, a single number will be yielded.

To find the portfolio variance, take the square root of the answer above

Therefore, let us proceed by addressing the variance of the portfolio in sequence.

I will generate ‘M1’, a row matrix, which holds the product of the transposed Wt.SD with the correlation matrix, containing 5 elements.

Be aware that to open the empty array space, you need to press down ctrl+shift+enter together.

To create a new value, we multiply M1 with the weighted standard deviation and call it ‘M2’.

We obtain M2 as 0.000123542, and its square root is the portfolio variance.

The outcome of the operation is a 1.11% portfolio variance of the group of 5 stocks.