Excel’s tools can’t handle this situation, unless you use array formulas. So, in these circumstances we need to weight the potential returns by their probability of occurrence. This is important to me because we often use probability distributions of potential asset returns to evaluate optimal portfolios (or generate an efficient frontier). There is another problem with the Covar() function and the Covariance tool: They don’t calculate a weighted variance/covariance 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. 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. While Excel’s built-in tools are perfectly functional, they don’t always work in exactly the way that I would like. For this reason, Excel provides the Covar() function and also the Covariance tool in the Analysis ToolPak add-in. It also has numerous uses in other fields. In particular, it is needed to calculate the standard deviation (volatility) of a portfolio, and is used to calculate the beta of an asset. The variance/covariance matrix is very important in finance. I finally decided to do something about one problem that has been bothering me for years. Because of the coming update, I have renamed the previous versions and updated the links in the posts.Įxcel doesn’t always do everything that you might want, or it doesn’t do it in a way that is sufficient for your needs. I should have it posted within a few days. The new version will require Excel 2010 or later. Watch this blog for details and download. Update 3 (October 24, 2014): New features are coming very soon in version 2.0. Update 2: New features added on February 7, 2009. Update: Please also see the variance/covariance matrix add-in for Excel 2003.
0 kommentar(er)
