This spreadsheet calculates the historical volatility of a stock. It uses returns data automatically downloaded from Yahoo.
Historical volatility is the standard deviation of an asset’s historical returns. The standard deviation is calculated over a moving time window.
The historical volatility of a stock is distinct from implied volatility of an option. The former represents past movements in price. The latter represents future expectations about price movements, and is calculated from the option price.
By comparing the historical volatility of the underlying to the implied volatility of the option, investors can judge if the option is cheap or expensive. If the implied volatility is high, then selling the option is sensible. However, if the implied volatility is low, the option is a good buy.
How to Calculate Historical Volatility
- Calculate the natural log of the current stock price to yesterday’s stock price. This is the continuously compounded return.
- Calculate the average return over a moving time window of n days. A value of n = 21 represent the typical number of trading days in a month, and is often used. Values lower than this tend to produce lots of noise in the results. The greater the time window, the smoother the results.
- Calculate the standard deviation of the returns over the moving time window.
- Annualize the daily standard deviation by multiplying by the square root of the number of days in a year. The average number of trading days in a year is 252.
Calculate Historical Volatility in Excel
The spreadsheet automates the steps described above, and is simple to use. Simply enter the stock ticker, the start and end dates, and the volatility window (i.e. the number of days over which the volatility is calculated).
The end date is set to NOW() by default, which gives the current date.
After clicking the button, the spreadsheet downloads returns data from Yahoo using VBA. Then, the chart will plot the historical volatility (based on the daily adjusted close)