Though there are several ways to measure the volatility of a given security, analysts typically look to the 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.

  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. How is standard deviation used to determine risk?

    Understand the basics of calculation and interpretation of standard deviation, and how it is used to measure risk in the ... Read Answer >>
  3. What does standard deviation measure in a portfolio?

    Dig deeper into the investment uses of and mathematical principles behind standard deviation as a measurement of portfolio ... Read Answer >>
  4. What is the difference between the expected return and the standard deviation of ...

    Learn about the expected return and standard deviation and the difference between the expected return and standard deviation ... Read Answer >>
  5. Implied Volatility

    Implied volatility is an important concept in option trading. Learn how it is calculated using the Black-Scholes option pricing ... Read Answer >>
  6. Which market indicators reflect volatility in the stock market?

    Learn the most commonly used technical indicators of stock market volatility that are watched by stock market traders and ... 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. Trading

    Volatility's Impact On Market Returns

    Find out how to adjust your portfolio when the market fluctuates to increase your potential return.
  3. 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.
  4. Investing

    Are Your ETFs Too Risky? Learn How to Evaluate Them

    Learn how to identify ETFs with greater risk and volatility. See why some investors include higher volatility ETFs in pursuit of greater returns.
  5. 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.
  6. Investing

    SPLV vs. LGLV: Comparing Low-Volatility ETFs

    Discover the major differences between two competitive low-volatility ETFs, and learn why one may be more suitable for your portfolio than the other.
  7. Insights

    Low Volatility? You Have Options

    With volatility at record lows, options have never been cheaper.
  1. Volatility Swap

    A forward contract whose underlying is the volatility of a given ...
  2. Risk-Adjusted Return

    A risk-adjusted return takes into account the amount of risk ...
  3. Risk Measures

    Statistical measures that are historical predictors of investment ...
  4. Risk

    The chance that an investment's actual return will be different ...
  5. Risk Management

    Risk management occurs anytime an investor or fund manager analyzes ...
  6. Sortino Ratio

    A modification of the Sharpe ratio that differentiates harmful ...
Hot Definitions
  1. Diversification

    Diversification is the strategy of investing in a variety of securities in order to lower the risk involved with putting ...
  2. Liquidity

    Liquidity is the degree to which an asset or security can be quickly bought or sold in the market without affecting the asset's ...
  3. Federal Funds Rate

    The federal funds rate is the interest rate at which a depository institution lends funds maintained at the Federal Reserve ...
  4. Call Option

    An agreement that gives an investor the right (but not the obligation) to buy a stock, bond, commodity, or other instrument ...
  5. Standard Deviation

    A measure of the dispersion of a set of data from its mean, calculated as the square root of the variance. The more spread ...
  6. Entrepreneur

    An entrepreneur is an individual who founds and runs a small business and assumes all the risk and reward of the venture.
Trading Center