How to Calculate the Volatility for a Portfolio of Stocks

The value of a stock portfolio varies from day to day. This is called volatility and it is a form of risk. Statisticians have worked out a way to measure the volatility called the “standard deviation,” which measures how spread out (or dispersed) the numbers are in a set of data. The higher the standard deviation, the greater the volatility, or risk, of the portfolio. Standard deviation is the square root of another volatility-related term known as “variance,” which measures the square of the deviation between a set of numbers and their average value.

​Read More​: How to Calculate the Variance in a Portfolio

In practice, calculating the volatility of a portfolio can become a burdensome undertaking as the number of stocks in the portfolio and the number of measurement days increases. There is a portfolio volatility formula:

Portfolio Volatility = (Variance(aS₁ + bS₂ + cS₃ + … xS_n))^1/2

Where:

n = number of stocks in the portfolio

a, b, c, … x are the portfolio weights of stocks S₁, S₂, S₃…S_n

S = stock’s return

The formula takes the variance of each stock’s return in the portfolio and then expresses it as a standard deviation by taking the square root of the sum. Unfortunately, figuring the variance of each stock’s return over each measurement day can be enormously complicated, as the portfolio weights will be constantly changing, and you must calculate the correlation coefficient between each pair of stocks in the portfolio.

What’s needed is a simplified way to measure the historical volatility (i.e. standard deviation) of your portfolio over time. You can use Excel to great advantage if you have the required input data, namely the daily return you earned on your portfolio. Daily return is simply today’s portfolio value minus yesterday’s, divided by yesterday’s value. In Excel, you create a row for each trading date in the measurement period containing the ending portfolio value and the calculated daily return.

Daily volatility of return is calculated using the Excel formula for standard deviation, STDEV.S over the range of rows. However, what you really need is the annualized volatility, so multiply the daily volatility by the square root of the number of trading days in the calendar year (approximately ​252 days​).

​Read More​: How to Calculate the Annualized Volatility

The Excel procedure is highly simplified and assumes you have the daily return figures for your portfolio (which should be available from your broker). Results may be skewed by several factors, including:

• Changes to the composition of your portfolio.
• Dividends.
• Frictional costs (i.e., commissions, bid-ask spreads, transfer taxes, etc.).
• Your data may not conform to a normal distribution.

Therefore, the results should be viewed as a rough approximation. If you really need more precision, you might consider indexing your portfolio, where you divide the daily results by a divisor that accounts for dividends, purchases, sales and other factors.

To interpret your results, it makes sense to compare your portfolio’s annual volatility with that of a suitable index. For instance, if your portfolio consists mostly of large- to medium-capitalization stocks, you can compare your volatility of returns with that of the S&P 500 stock index.

Your portfolio may be more or less volatile than its relevant index, but that alone doesn’t tell you whether the returns you earned were worth the risks you took. For that, you can turn to another metric, the Sharpe Ratio.

The Sharpe Ratio subtracts the risk-free rate (represented by the three-month Treasury bill rate) from the portfolio’s return and divides this number by the portfolio’s standard deviation (i.e. volatility). In other words, it is return divided by risk. The higher the Sharpe Ratio, the more return you are getting for the risk you are taking.

For example, imagine your portfolio’s annual return is 25 percent with a standard deviation of 10, and the T-Bill returns 5 percent. The fund’s Sharpe Ratio is 25 percent minus 5 percent, or 20 percent, divided by the standard deviation of 10, giving a ratio of 2.0.

A Sharpe ratio this high is rated as very good. A ratio below 1.0 is suboptimal, while ratios greater than 3.0 are considered excellent. Comparing Sharpe Ratios is an important way of gauging whether you are getting a good return for the risks you are taking.

​Read More​: Advantages & Disadvantages of Using Sharpe Ratio