We can gauge the riskiness of a given portfolio by the riskiness of the instruments that make up the portfolio. However, the portfolio risk or volatility of portfolio returns is not necessarily equal to the sum of each instrument’s risk as given by their respective volatility. Instruments in a portfolio may not be independent of each other, hence portfolio volatility needs to factor in the impact of correlations.
Volatility for a portfolio may be calculated using the statistical formula for the variance of the sum of two or more random variables which is then square rooted. Alternatively, the volatility for a portfolio may be calculated based on the weighted average return series calculated for the portfolio. Both of these methodologies are discussed below.
Portfolio Volatility by formula
Consider a portfolio of three assets X, Y and Z with portfolio weights of a, b and c respectively. The portfolio volatility is:
Variance (X) = Variance in asset X’s returns, i.e. X’s returns volatility squared (σx2)
Variance (Y) = Variance in asset Y’s returns, i.e. Y’s returns volatility squared (σy2)
Variance (Z) = Variance in asset Z’s returns, i.e. Z’s returns volatility squared (σZ2)
ρxy = correlation coefficient of X and Y returns
ρxz = correlation coefficient of X and Z returns
ρyz = correlation coefficient of Y and Z returns
If the assets in the portfolio are independent of each other the correlation coefficient terms in the equation above would be zero. If the assets in the portfolio are perfectly correlated with each other then the volatility of the portfolio would simply be the weighted average sum of the asset return volatility.
Let us consider the following example:
Our portfolio consists of two stocks ABC and XYZ, whose weights within the portfolio are 89% and 11% respectively. The stock price data for month of January 2009, our period of analysis, is:
Next, calculate the daily return time series from the price data. Determine the natural logarithm of the ratio of successive (consecutive) prices to get the daily returns series:
The resulting return series for each stock is:
We then calculate the variance in daily returns of the stocks using the EXCEL function VAR() applied to the each stocks return series in turn. To review the calculation methodology for variance using VAR() view this earlier post:
The variance for stock ABC works out to 0.141% whereas the variance for stock XYZ works out to 0.578%. The standard deviation or daily volatilities equal to the square root of these variances. They are 3.76% and 7.60% for stocks ABC and XYZ respectively.
Next, we calculate the correlation coefficient for Stock ABC and XYZ returns. To review the calculation methodology for correlation coefficient see the following post:
The correlation coefficient for Stocks ABC and XYZ returns is 0.64014.
Using the formula given above we can now calculate the portfolio volatility:
Portfolio volatility = Root(89%2×0.141%+11%2×0.578%+2×89%×11%×0.64014×3.76%×7.60%)=3.93%. Note that this is daily portfolio volatility.
Portfolio volatility by weighted average returns
As you can see this formula method for calculating portfolio volatility can get pretty cumbersome as the number of instruments within the portfolio increases. The formula requires the calculation of the variance for each instrument within the portfolio as well as the correlation coefficients for each pair of instruments.
A simpler and more practical way of handling this calculation is to first derive a weighted average return series for the portfolio. We calculate the weighted average return series for the portfolio for each time point for which data is available as the sum of (weight×return) across all instruments in the portfolio.
We illustrate this for our two asset sample portfolio below:
The method to calculate the weighted average portfolio return on 2-Jan-2009 is as follows:
Weighted Return for Portfolio on 2-Jan-2009 = Weight of Stock ABC in portfolio × Return of Stock ABC on 2-Jan-2009 + Weight of Stock XYZ in portfolio × Return of Stock XYZ on 2-Jan-2009 = 89%× (-1.19%) + 11% × (-1.65%) = -1.24%.
Once we calculate the weighted average return series for the portfolio, we can treat it just like the return time series for individual instruments. The daily portfolio volatility is then calculated using EXCEL’s STDEV() function applied to the weighted average return series of the portfolio. To review the calculation methodology for daily volatility using the STDEV() function see the following post:
The portfolio volatility calculated using this methodology works out to 3.93%, i.e. the same result as that derived using the statistical formula methodology. Again it should be noted that we have calculated the daily volatility here. In order to calculate portfolio volatility for a J-day period, we scale the daily volatility for the portfolio using the following formula:
J-day volatility = Daily volatility ×Root(J)
Like this post – check out the new book – Portfolio Optimization Models in Excel, Revised Edition – EXCEL templates and dataset included.