Risk Management: Introduction to Variance and Covariance

How to get the variance-covariance matrix for your portfolio?

Before this, we’ve spoken on variance and covariance to see how risky your assets are. Of course, that won’t be enough, which is why we’ll now look into the variance-covariance matrix — a step away from looking into the variance-covariance correlation matrix, which is the thing that will tell you how the stocks in your portfolio correlate with one another.

Honestly, the steps are long, and it’s okay if you don’t feel like learning how to do it from scratch. You can skip all of these lessons and head straight to this Google sheet file, which allows you to reach the portfolio correlation step, which is a step after this one, right away. All you need to do is fill in the ticker name for the stocks that you have and its weight. All credit for the sheet file goes to John Mihalik who published it on his Medium page.

By the way, the weight is how many percent of your portfolio the stock constitutes. For example, the total money you allocated to invest is $1,000. Out of that $1,000, $100 is invested in Stock A. So, Stock A’s weight is 10%.

These are some of the things that is included in the Google sheet file:

It’s super helpful! You can see from the example above how different stocks in your portfolio correlate to one another, like how INTC and META have 0.483 in correlations (the closer it is to 1, the more correlated they are).

The key to risk management is to make sure your stocks are diversified so that some stocks shouldn’t be strongly correlated to another. If a few of them are, it’s okay, but make sure not too many of them are strongly correlated — that would mean that your portfolio is not diversified well.

If getting to it is all you need, the stuff above is already enough for your needs. If you want to learn how to do it from scratch — here we go!

Step 1: Get the historical price data

Usually, you’d need at least one year’s worth of price data, but for the sake of learning (and me being an energy conservationist) we’ll just use 5-day data. This is just to get you to understand the process. I would usually go to Yahoo! Finance to download the price data. For this example, I will use Microsoft Excel, and I will look into 5 stocks: Apple (AAPL), Tesla (TSLA), Intel (INTC), Meta (META), and Microsoft (MSFT).

Step 2: Know the formula to create a variance-covariance matrix as follows

This is the formula for it, whereby:

k = number of stocks in the portfolio (in our case 5 stocks)

n = number of observations (in our case 5 days)

X = the n x k excess return matrix, where Daily return of stock — Average return of stock.

XT= transpose matrix of X (we’ll also calculate this shortly)

Step 3 Find X:

To find the value of the n x k excess return matrix, you’ll need to find the daily return for each stocks, and find its average.

First, lay down each of the stocks’ closing prices, and then find the daily rate of returns. It’s calculated like this:

Rate of return = (Day2/Day1) — 1

And convert the decimals to percentage format for easier understanding.

Second, look for the average return:

In order to get the average returns for each stock, you can just use the =AVERAGE function and just drag the returns for each stock. Excel will do the rest for you.

Third, create the “excess return matrix” table. It’s just a table containing the result of each day’s returns minus the average return. The logic behind this is to see how far away the return is from its average path.

Also, before I forget, since we’ve already got the n (number of days) and k (number of stocks) values, we put them right away so that it’s easier to refer to them later.

Step 4: Get to the XT X part of the formula.

The XTX can be pronounced as hee/hee, just kidding, this is getting too long… gotta lighten it up a bit, right? Anyhow, it is pronounced as X transposed by X. To do this, you’ll need to create the matrix table for it, which looks like this:

Simple, isn’t it? Of course, adjust according to the number of stocks you have in your portfolio. So, what are we going to do with this?

We use the functions MMULT and TRANSPOSE on the excess return matrix or X that we have calculated above. How you want to do it is highlight the matrix table above, the table right above us, starting from one corner, and ending with the other end. In our case, it’s where AAPL meets AAPL and ends with TSLA meets TSLA.

After that, you insert the formula and highlight the X table twice. It would look like this:

I know it doesn’t look that pretty, but yeah… So, you highlight the ones in green; insert the formula, highlight the ones in yellow, highlight the same thing again (marked in blue), and then press CTRL + SHIFT + ENTER. You can’t press the ENTER button as usual in this case because this is an array function. Do you need to know what that is? Not really, for now.

You’ll then get this, which gives you the k x k matrix:

Yes, the whole table is XTX. Not just a single number.

Stage 5: Drink a glass of water, stay hydrated, and see where we are now.

It’s important to stay hydrated, but it’s more important to get a sense of where we are in the whole process. Right now, we’ve found the value of all the things checked below:

Yes, we’ve got all the necessary values now. Thank God and excel.

Stage 6: Churn the heck out of it!

Now that we’ve got the necessary elements to complete the formula, it’s time to process it, and to do this… you’ll have to create another table! And this one look just like the one up there. This:

Once you’re done, do the same thing again, which is by selecting all the empty boxes from corner to corner, and then start the formula by pressing in = followed by selecting all of the contents in the XTX table, and dividing it by n (number of days). In our case, we just use 5 days, so n=5.

Let’s see how it would work out in the wild:

After pressing CTRL + SHIFT + ENTER you’ll get this result:

Mind you, the number tends to be very small as you can see from AAPL by AAPL which is at 3.27894E-05, whereby the E-05 means it must be divided by a 1 with five zeros (100,000); so, 3.27894/100,000 = 0.0000327894.

Yes. We got ’em. Finally. This table is useful for you to understand the covariance between one asset in your portfolio to another. Then why is it called variance-covariance? Where’s the variance? The variance is when you look at the covariance of a stock against itself, for example, AAPL & AAPL at the top left — a covariance of covariance is… a variance! That’s where variance is.

Bottom Line

The variance-covariance matrix helps when you want to easily see the covariance between more than two stocks in your portfolio.
This covariance matrix can be further processed into a correlation matrix.
A covariance matrix is a square matrix that provides a measure of how much two random variables change together. It shows the covariance between each pair of variables in a dataset.
A correlation matrix is a standardized version of the covariance matrix. It measures the linear relationship between these variables.
Both will have three points, 1 which indicate an absolute positive correlation, 0 which indicates no correlation, and -1 which indicates a negative correlation.
From our example above, we can see that many of the stocks are positively correlated, albeit being quite far from 1.
Actually, all you need to understand is the concept, then you’ll be able to utilize any available tools — free or not.

The key takeaways/market update is a series by AxeHedge, which serves as an initiative to bring compact and informative In/Visible Talks recaps/takeaways on leading brands and investment events happening around the globe.

Do keep an eye out for our posts by subscribing to our channel and social media.

None of the material above or on our website is to be construed as a solicitation, recommendation or offer to buy or sell any security, financial product or instrument. Investors should carefully consider if the security and/or product is suitable for them in view of their entire investment portfolio. All investing involves risks, including the possible loss of money invested, and past performance does not guarantee future performance.