Pages Navigation Menu

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 :

Calculating Portfolio Volatility

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 :

Calculating Volatility for large portfolios

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

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

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

Portfolio Volatility

 

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.

Portfolio Volatility

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.

3 Comments

  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.

Trackbacks/Pingbacks

  1. Calculating Correlation in Excel - ThinxLabs | ThinxLabs - [...] can download the excel spreadsheet to calculate correlation by clicking here. Suggested Reading : Calculating correlation in excel is a start, however you …
  2. Calculating Portfolio Volatility in Excel | ThinxLabs - [...] you out if we can.You can download the excel spreadsheet to calculate correlation by clicking here. Suggested Reading : Read the wikipedia …
  3. Capital Market Line and the Efficient Frontier N Assets - [...] about how to calculate portfolio volatility for large portfolios, this can be done in one of our previous posts …
  4. Capital Market Line & Efficient Frontier - N Assets - ThinxLabs | ThinxLabs - [...] is best done with the use of excel. This topic would benefit from reading through our articles on portfolio …

Leave a Reply

Buy the VaR eBook on Amazon for only $4.99 Click Here