How to calculate volatility
In order to analyze volatility, you need to create a data set that tracks the price or value changes of a stock, your portfolio, or an index at a regular interval (such as daily). If you know a stock's or an index's daily closing price, ideally over a long period of time, then you can compute as a percentage the amount the stock's price or index's value changes from day to day.Investors use this data to calculate a stock price or index's standard deviation, which is used as a proxy measure for volatility. The best way to calculate standard deviation is to use a spreadsheet program such as Microsoft Excel, so keep reading for a complete example of calculating price or value volatility using Excel.
Calculating volatility using Microsoft Excel
While using a large data set is necessary to achieve accuracy by way of statistical significance, here we'll work with just 10 days of closing values for the S&P 500 (SNPINDEX:^GSPC).
- Pro tip: simply drag this formula down to the end of the dataset in row 12 for all values in Column D to populate.
Calculating portfolio volatilityYou may be interested in learning how to calculate the volatility of your portfolio, given that most people don't hold just a single stock position. Portfolio volatility is a measure of portfolio risk, meaning a portfolio's tendency to deviate from its mean return. Remember that a portfolio is made up of individual positions, each with their own volatility measures. These individual variations, when combined, create a single measure of portfolio volatility.To calculate the volatility of a two-stock portfolio, you need:
- The weight of stock 1 in the portfolio
- The weight of stock 2 in the portfolio
- The standard deviation (volatility) of stock 1
- The standard deviation of stock 2
- The covariance, or relational movement, between the stock prices of stock 1 and stock 2