How to Calculate Volatility in Excel

/How to Calculate Volatility in Excel

How to Calculate Volatility in Excel

Wild-swinging oil prices have caused some chaos, or “volatility,” in the financial markets recently. We’ve also heard a lot in the financial media regarding the strong performance of “low volatility” funds.

But what exactly is “volatility” and how do we measure it?

We’ve posted some thoughts on the low volatility anomaly (e.g. Avoid High Beta Stocks. Period.)

But the use of volatility is somewhat ambiguous.

In the studies we’ve conducted we’ve referenced  “beta” and Idiosyncratic volatility (“ivol”).

We thought it might make sense to take a quick break and describe how these concepts are calculated. We first describe 3 ways in which we can describe “volatility” and then we provide a spreadsheet so you can see these calculations in action.

Standard Deviation:

When we talk about a security’s volatility, we first think of the “standard deviation” of stock returns, which measures the degree of fluctuations in relation to its mean return over a period of time. This measure is calculated independently of the market and only requires data on the stock.

BETA:

While Standard Deviation measures the disparity of a security’s return over a period of time, “BETA”, another widely used metric, measures the co-movement of this security with the market. BETA can be calculated by regressing daily stock returns on a market benchmark (such as value weighted CRSP) over a period of time.

Idiosyncratic volatility (IVOL):

Business school professors tell us that there are 2 types of risk: systematic risk and unsystematic risk. Systematic risk can be estimated by Beta. The idiosyncratic risk is the portion of risk that unexplained by BETA. We calculate Idiosyncratic volatility (IVOL) as the standard deviation of the residuals from a regression that uses Beta to estimate the relationship between a given asset and the market.

Calculation Example:

  1. We use Amazon (Ticker: AMZN) stock as a single stock example, and use the value weighted CRSP index as the market benchmark. If forming a portfolio on 1/31/13, we would use daily returns from 1/31/12-1/31/13 to calculate beta and IVOL. (We use daily data to improve frequency and accuracy.)
  2. BETA calculation follows the basic methodology in Betting Against Beta.
  3. IVOL calculation follows the basic methodology in IVOL and the Cross-Section of Expected Returns.

If you are interested, please click below to download the excel sheet.

Microsoft Excel - Beta-and-IVOL-Example-Sheet.xlsx_2014-11-24_17-41-21

———————————–

Beta-and-IVOL-Example-Sheet

———————————–

 


  • The views and opinions expressed herein are those of the author and do not necessarily reflect the views of Alpha Architect, its affiliates or its employees. Our full disclosures are available here. Definitions of common statistics used in our analysis are available here (towards the bottom).
  • Join thousands of other readers and subscribe to our blog.
  • This site provides NO information on our value ETFs or our momentum ETFs. Please refer to this site.

Print Friendly, PDF & Email

About the Author:

Wes Gray
After serving as a Captain in the United States Marine Corps, Dr. Gray earned a PhD, and worked as a finance professor at Drexel University. Dr. Gray’s interest in bridging the research gap between academia and industry led him to found Alpha Architect, an asset management that delivers affordable active exposures for tax-sensitive investors. Dr. Gray has published four books and a number of academic articles. Wes is a regular contributor to multiple industry outlets, to include the following: Wall Street Journal, Forbes, ETF.com, and the CFA Institute. Dr. Gray earned an MBA and a PhD in finance from the University of Chicago and graduated magna cum laude with a BS from The Wharton School of the University of Pennsylvania.
  • Bruno V.

    Wesley: why calculate daily and not monthly volatility? Does it make a difference? Cheers, -Bruno

  • you could…just meant for educational purposes.

    The higher frequency estimates tend to be better–if you have the data available…