Calculating Portfolio Volatility in Excel
Calculating Portfolio Volatility
We have looked at how to calculate historical volatility for a single asset from a time series in previous blog posts. The next step is to understanding calculating portfolio volatility – particularly large ones.
Calculating Portfolio Volatility for 2 assets is actually pretty simple. The formula is given as :
What this tells us is that we need to have the weights for each of the assets in the portfolio and also the correlation between the 2 assets defined by the Greek letter ρ (rho) in the equation above. Once we know these numbers along with the volatility it is a simple matter to calculating portfolio volatility. Initially we get the portfolio variance, from which we can take the square root to determine the volatility of the 2 asset portfolio.
Now if you were charged with calculating portfolio volatility for a 3-asset portfolio this equation would become somewhat more cumbersome. You would need to factor in the correlation between 3 assets and the corresponding 3 sets of correlations. For 4 assets this becomes even more onerous with 6 correlations that need to be accounted for. Clearly this is not something that is really feasible to do without the aid of some computational power once you get beyond 2 assets. I suspect this is the reason why most financial exams at University level or even professional ones like the Professional Risk Manager (PRM) or Financial Risk Manager (FRM) or the Chartered Financial Analyst (CFA) dont really test candidates with calculating portfolio volatility beyond the 2 asset model. In many ways this is adequate as this instills the theory of calculating portfolio volatility.
However in practice in the financial world calculating portfolio volatility for a large number of assets is quite often necessary. The simplest way of course is to use excel, more than likely you will only have to set up a template once, after which you can simply update your time series and get the portfolio volatility figure. You could also go a step further and write a small VBA function which helps you in calculating portfolio volatility for large portfolios. The advantage of VBA functions is that it can be a lot cleaner and quicker to use. The other very useful excel feature that should be used when calculating portfolio volatility (or in many other situations for that matter) is named ranges. If you are not familiar with named ranges we have a brief video tutorial to get you started here.
Calculating Portfolio Volatility : The Formulas
A quick recap of the theory (and the formulas) may be beneficial before looking at the excel spreadsheet. Dont be daunted by the huge matrix – just remember its just a big multiplication and excel can do it through a very simple function call for you (MMULT(Matrix1 , Matrix2)
The main formula describing Calculating portfolio Volatility f is :
Portfolio Variance Matrix Multiplication
where : x’ is the transpose of the weights and V is the covariance matrix of the assets, while x is the vector of asset weights in the portfolio. We can expand this to:
Portfolio Variance Matrix Multiplication Full
Notice that we are working with covariances rather than correlations. Correlation is essentially a normalisation of the covariance number which is bounded it between -1 and 1 (far more meaningful at an intuitive level than the covariance number which can be pretty meaningless when out of context). The formula for calculating the correlation from the covaraince is as follows:
In the financial markets it is much more common to work with the correlation matrix rather then the covariance matrix. As a result we can adapt our approach slightly to use a correlation matrix, it can be rewritten as follows:
Thats the summary of the theory of calculating portfolio volatility, in practice you would set up this approach in an excel spreadsheet to calculate portfolio volatility. The key excel function to use here is MMULT which stands for Matrix Multiplication. You can download an example spreadsheet which will lead you through how to do this from scratch for a portfolio of 5 assets. We show you how to set it up from the point of getting a history of asset prices from Google Finance and calculating historical volatilities and correlation from that to calculating portfolio volatility. This spreadsheet should be pretty easy to use and also extendable very easily should you want to use more assets. Simply create more named ranges in excel and copy the formulas down to extend it, it is a pretty simple spreadsheet so you should have very little trouble. If you do run into any problems just post a comment and we will help you out if we can.
Click on the button below to download the file. Please note that the file is only available to registered members, if you are not a member already you can join easily by signing up or using your Facebook or Twitter login to access the file.
Suggested Reading : Read the wikipedia article on Modern Portfolio Theory which talks about calculating portfolio volatility for more theory around this topic and further your knowledge.