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
Calculating Volatility of 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(aS1 + bS2 + cS3 + … xSn))1/2
n = number of stocks in the portfolio
a, b, c, … x are the portfolio weights of stocks S1, S2, S3…Sn
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.
Calculating Volatility in Excel
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
Complications Diminish Calculated Volatility Accuracy
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.
- 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.
Understanding 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
- To eliminate the potential for mistakes in each of the calculations, it’s advisable to use an Excel spreadsheet rather than computing all calculations manually. The Excel program allows you to input a formula for each calculation, thereby minimizing the time it will take you to assess the volatility of your stock portfolio.
- You should use volatility as just one of many tools for assessing the risk of your stock investments. For example, if the volatility of your stock portfolio is low for the period, future fluctuations of the stock price outside of the standard deviation can be the result of other economic factors that affect the price of all stock rather than the inherent risk of one particular stock.
Eric Bank is a senior business, finance and real estate writer, freelancing since 2002. He has written thousands of articles about business, finance, insurance, real estate, investing, annuities, taxes, credit repair, accounting and student loans. Eric writes articles, blogs and SEO-friendly website content for dozens of clients worldwide, including get.com, badcredit.org and valuepenguin.com. Eric holds two Master's Degrees -- in Business Administration and in Finance. His website is ericbank.com.