A:

Although there are several ways to measure the volatility of a given security, analysts typically look at historical volatility. Historical volatility is a measure of past performance. Because it allows for a more long-term assessment of risk, historical volatility is widely used by analysts and traders in the creation of investing strategies. (Want to improve your Excel skills? Take Investopedia Academy's Excel Course.)

To calculate volatility of a given security in Microsoft Excel, first determine the time frame for which the metric will be computed. A 10-day period is used for this example. Next, enter all the closing stock prices for that period into cells B2 through B12 in sequential order, with the newest price at the bottom. Note that you will need the data for 11 days to compute the returns for a 10-day period.

In column C, calculate the interday returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's (MCD) closed at $147.82 on the first day and at $149.50 on the second day, the return of the second day would be (149.50/147.82) - 1, or .011, indicating that the price on day two was 1.1% higher than the price on day one.

Volatility is inherently related to standard deviation, or the degree to which prices differ from their mean. In cell C13, enter the formula "=STDV(C3:C12)" to compute the standard deviation for the period.

As mentioned above, volatility and deviation are closely linked. This is evident in the types of technical indicators that investors use to chart a stock's volatility, such as Bollinger Bands, which are based on a stock's standard deviation and the simple moving average (SMA). However, historical volatility is an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year.

The table below shows the volatility for McDonald's within a 10-day period:

The example above used daily closing prices, and there are 252 trading days per year, on average. Therefore, in cell C14, enter the formula "=SQRT(252)*C13" to convert the standard deviation for this 10-day period to annualized historical volatility.

RELATED FAQS
  1. What is the best measure of a stock's volatility?

    Understand what metrics are most commonly used to assess a security's volatility compared to its own price history and that ... Read Answer >>
  2. What is the difference between standard deviation and average deviation?

    Understand the basics of standard deviation and average deviation, including how each is calculated and why standard deviation ... Read Answer >>
  3. What is the difference between standard deviation and Z-score?

    Understand the basics of standard deviation and Z-score, and learn how each is calculated and used in the assessment of market ... Read Answer >>
  4. Which market indicators reflect volatility in the stock market?

    Stock traders use the volatility index (VIX), the average true range (ATR) indicator, and Bollinger Bands to indicate volatility ... Read Answer >>
  5. Volatility From the Investor's Point of View

    Increased volatility in the stock market provides greater opportunities to profit for both long- and short-term traders. Read Answer >>
Related Articles
  1. Investing

    Why Standard Deviation Should Matter to Investors

    Think of standard deviation as a thermometer for risk, or better yet, anxiety.
  2. Investing

    Roller coaster 2016 for Stocks? Exploring Global Stock Volatility

    Find out how much volatility global equity investors are in for during 2016 by seeing how much they've experienced over the past five years.
  3. Trading

    Why Volatility is Important For Investors

    Many investors realize the stock market is a volatile place to invest their money, learn how volatility affects investors and how to take advantage of it.
  4. Investing

    Calculating volatility: A simplified approach

    Though most investors use standard deviation to determine volatility, there's an easier and more accurate way of doing it: the historical method.
  5. Investing

    How to Take Advantage of Volatility as an Investor

    Everyone talks about the downside of volatility, but it has its benefits too, including opportunities to investment entry points at lower prices.
  6. Investing

    Volatile Stocks: Great, If You Have The Stomach

    Volatile stocks can be a lucrative opportunity for short-term traders. For buy-and-hold investors, it's a much different story.
  7. Investing

    3 Reasons to Ignore Market Volatility (VIX)

    If you can keep your head while those about you are losing theirs, you can make a nice return in roiling markets.
  8. Investing

    Understanding The Sharpe Ratio

    The Sharpe ratio describes how much excess return you are receiving for the extra volatility that you endure for holding a riskier asset.
  9. Investing

    Stock Market Risk: Wagging The Tails

    The bell curve is an excellent way to evaluate stock market risk over the long term.
RELATED TERMS
  1. Historical Volatility - HV

    Historical volatility is a statistical measure of the dispersion ...
  2. Volatility Ratio

    The volatility ratio is a technical measure used to identify ...
  3. Empirical Rule

    The empirical rule is a statistical rule stating that for a normal ...
  4. Implied Volatility - IV

    The estimated volatility of a security's price derived from an ...
  5. Risk Management

    Risk management occurs anytime an investor or fund manager analyzes ...
  6. Bollinger Band®

    A Bollinger Band® is a set of lines plotted two standard deviations ...
Trading Center