Update: Please also see the variance/covariance matrix add-in for Excel 2003.
Update 2: New features added on February 7, 2009. Please see end of post.
Update 3 (October 24, 2014): New features are coming very soon in version 2.0. It is complete, except for testing. Watch this blog for details and download. The new version will require Excel 2010 or later. I should have it posted within a few days. Because of the coming update, I have renamed the previous versions and updated the links in the posts.
Excel doesn’t always do everything that you might want, or it doesn’t do it in a way that is sufficient for your needs. That’s why we have VBA and add-ins. I finally decided to do something about one problem that has been bothering me for years. The variance/covariance matrix is very important in finance. In particular, it is needed to calculate the standard deviation (volatility) of a portfolio, and is used to calculate the beta of an asset. It also has numerous uses in other fields. For this reason, Excel provides the Covar() function and also the Covariance tool in the Analysis ToolPak add-in. While Excel’s built-in tools are perfectly functional, they don’t always work in exactly the way that I would like. In particular, the Covariance tool doesn’t create a “live” variance/covariance matrix, so if you change the data you have to recreate the matrix. It is this point that prompted me to write my own add-in that solves what I perceive to be the shortcomings of Excel’s offerings.
There is another problem with the Covar() function and the Covariance tool: They don’t calculate a weighted variance/covariance matrix. This is important to me because we often use probability distributions of potential asset returns to evaluate optimal portfolios (or generate an efficient frontier). So, in these circumstances we need to weight the potential returns by their probability of occurrence. Excel’s tools can’t handle this situation, unless you use array formulas. My new add-in will create a weighted variance/covariance matrix if you supply the weights. If you don’t give it any weights, then it assumes equal weighting and works much like Excel’s built-in tools.
Covariance is similar to the variance, which measures average squared deviations from the mean for a single variable. Covariance, as the name suggests, measures how two variables co-vary, and it is calculated very much like the variance. In fact, the (single variable) variance is really just the covariance of that variable with itself. The equation for covariance is usually presented as:
or, we can show it in matrix form as:
where D’ is the transpose of the difference matrix, D is the difference matrix, and N is the number of variables. The difference matrix is simply a matrix of each observation less the mean. Using Excel’s matrix functions, we can calculate the covariance function in one swift move with:
=MMult(Transpose(Data – Mean), (Data – Mean))/N
where Data is your range of data, and Mean is an array of means for each variable. That is pretty straightforward if you have pre-calculated the means. Your formula might end up looking something like this (array entered):
=MMult(Transpose(B2:D11 – B12:D12), (B2:D11 – B12:D12))/10
assuming that your data (3 variables, 10 observations each) are in B2:D11, and the means are calculated in B12:D12.
Now, if we need to calculate a probability-weighted covariance matrix, we would slightly modify that to:
=MMult(Transpose(A2:A11*(B2:D11 – B12:D12)), (B2:D11 – B12:D12))
assuming that the probabilities are located in A2:A11. Note that we aren’t dividing by 10, because that assumes equal probability for each outcome (1/10). Instead, we are allowing for the probabilities to differ. Also, note that your means need to be probability-weighted averages (expected values), instead of just an equal-weighted average.
That is the background. When you run this add-in, you will notice that the formula looks much more complex. The reason is that I wanted the covariance matrix to be completely self-contained, so I calculated the means within the formula. Also, for the equally weighted (you might think of it as unweighted) covariance matrix, I actually generate an array of weights within the formula. So, the formula created by the add-in will be ugly and difficult to follow. Just think of the above equations if you are trying to understand it.
The Variance/Covariance Add-in
The cov-matrix.xlam add-in works with Excel 2007 and mimics Excel’s Covariance tool, except for some additional features. Here is a screen shot of the dialog box:
Just like Excel’s tool, it allows you to specify an input range with the data grouped by columns or rows and, optionally, allows you to have data labels in the first row or column. It also allows you to specify the upper-left corner of the output range. Those are the similarities.
My add-in is different in that it allows you to specify a range that contains weights (probabilities) to create a weighted variance/covariance matrix. Note that the weights must sum to 1, or else you will get an error message. Also, the weights must have the same orientation as the data in the input range. This is because the add-in uses the MMult() and Transpose() functions to create the matrix, and if the orientations don’t match the formula would return a #VALUE error. If you don’t specify weights, then this add-in will create either a population (same as Excel) or sample variance/covariance matrix.
Also, unlike Excel’s Covariance tool, this add-in allows you to specify whether to show the entire matrix, or just the upper or lower portion. Since a covariance matrix is symmetric around the diagonal, you only need to see half of it and that is all that Excel provides. My add-in always fills in the entire matrix, but it can hide part of it by setting the font color equal to the background color. This is valuable because having the entire matrix makes other calculations (e.g., portfolio standard deviation) much easier.
Finally, this add-in allows you to have a title above the matrix and it also has a Reset All button to reset everything to the defaults. The output will look something like the picture below:
But wait, there’s more! The add-in creates a live variance/covariance matrix, so you don’t need to recreate it if you edit your data. It will update automatically. Excel’s Covariance tool only enters formulas along the diagonal (for the variances), but not for the covariances. I always found that particularly strange as they could just as easily insert the Covar() function into those off-diagonal cells.
The add-in is provided without charge for any usage, even commercial. To install the add-in click the Office button and then the Excel Options button. At the bottom of the options dialog box, set Manage to “Excel Add-ins” and then click the Go button. Finally, browse to the location where you saved the add-in and load it. When installed and loaded, you will find the following button on the Data tab:
Click that button to run the add-in.
The source code can be viewed from within the VBA editor. Basically, the code shows the dialog box, validates the input, and the constructs a string that will be entered as an array formula on the worksheet. This creates a formula in your worksheet that is independent of the add-in. There are no user-defined functions in the output, so once you have created the variance/covariance matrix you no longer need the add-in unless you add additional data points or variables.
Please let me know if you find any bugs, inconsistencies, or just things that irritate you. Of course, I’d like to know if you find it useful as well.
Known Limitations: Because this add-in creates an array formula that uses the matrix functions, it is limited to 73 variables at most.
Update: On January 19, 2009 the add-in was updated. It will now calculate either a population or sample variance/covariance matrix. I also fixed a small validation bug that does not affect the results.
Update 2: On February 7, 2009 the add-in was updated to allow for the output to be on a different sheet than the input range. Previously, that would work but the formula would result in an error. The variance/covariance matrix add-in for Excel 2003 has been updated as well.
Update 3: On June 13, 2009 I uploaded a new version of the add-in. I implemented the fix that was mentioned in this comment.
If you have previously downloaded the add-in, you may wish to download it again.
Commenting is not available in this channel entry.