Making Finance Easy to Learn

# 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 :

Portfolio Variance

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:

Correlation Coefficient

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:

Portfolio Volatility

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.

### Kartik Natarajan

I write about Financial related topics on this site. My aim is to try and express complicated topics simply and effectively. I have years of experience in the financial industry - mainly in derivatives, derivatives pricing and risk management. You can find me on Google+

1. Also, I saw that sometimes you use monthly returns, sometimes you use daily returns, does this choice have some relationship with the holding period of your portfolio or something?
Say, if I have a portfolio to manage for 5 month, should I use daily returns or monthly for the stocks to get volatility and correlation?
Besides, how long for the period of the stock prices should I choose?
AND one more question, if I have not only the long stocks but also short some stocks, say a market neutral, dollar neutral portfolio, how do I get the correlation and the Var in this situation?
Probably too many questions, if you would bother to answer them, I would be much appreciated.
Thanks!

• 1). The returns you use (monthly , weekly , daily) depends on holding period and also how easily data is available. If your holding period is weekly , daily volatility is not so relevant to you and weekly makes more sense.
2). The portfolio you manage for 5 months – use the returns depending on the holding period of the assets in the portfolio.
3). You should use 1 year worth of data as this is generally accepted as standard in the industry.
4). Check elsewhere on this site for a posting on VaR and a downloadable spreadsheet. In theory you can use variance-covariance to calculate the VaR for a long short portfolio.

Hope that helps.

• Thanks a lot.