How to Calculate Volatility in Excel

How to Calculate Volatility in Excel

December 19, 2014 Investor Education, Introduction Course, Low Volatility Investing
Print Friendly
(Last Updated On: May 2, 2017)

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

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

 


Note: This site provides no information on our value investing ETFs or our momentum investing ETFs. Please refer to this site.


Join thousands of other readers and subscribe to our blog.


Please remember that past performance is not an indicator of future results. Please read our full disclaimer. 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. This material has been provided to you solely for information and educational purposes and does not constitute an offer or solicitation of an offer or any advice or recommendation to purchase any securities or other financial instruments and may not be construed as such. The factual information set forth herein has been obtained or derived from sources believed by the author and Alpha Architect to be reliable but it is not necessarily all-inclusive and is not guaranteed as to its accuracy and is not to be regarded as a representation or warranty, express or implied, as to the information’s accuracy or completeness, nor should the attached information serve as the basis of any investment decision. No part of this material may be reproduced in any form, or referred to in any other publication, without express written permission from Alpha Architect.


Definitions of common statistics used in our analysis are available here (towards the bottom)




About the Author

Wesley R. Gray, Ph.D.

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…