Exponentially Weighted Historical Volatility in Excel-Volatility Analysis in Excel

Subscribe to newsletter

Historical volatility (HV) is a useful measure to gauge market uncertainty. Recall that,

In finance, volatility (usually denoted by σ) is the degree of variation of a trading price series over time, usually measured by the standard deviation of logarithmic returns. Historic volatility measures a time series of past market prices… Investors care about volatility for at least eight reasons:

  1. The wider the swings in an investment’s price, the harder emotionally it is to not worry;
  2. Price volatility of a trading instrument can define position sizing in a portfolio;
  3. When certain cash flows from selling a security are needed at a specific future date, higher volatility means a greater chance of a shortfall;
  4. Higher volatility of returns while saving for retirement results in a wider distribution of possible final portfolio values;
  5. Higher volatility of return when retired gives withdrawals a larger permanent impact on the portfolio’s value;
  6. Price volatility presents opportunities to buy assets cheaply and sell when overpriced;
  7. Portfolio volatility has a negative impact on the compound annual growth rate (CAGR) of that portfolio
  8. Volatility affects pricing of options, being a parameter of the Black–Scholes model. Read more

Historical volatility is usually calculated by using the simple moving average of the historical returns. This approach works well when the market is in a “normal” condition. However, when there is a shock in the market, volatility increases and the equally weighted HV starts revealing its drawbacks, i.e.

Subscribe to newsletter https://harbourfrontquant.beehiiv.com/subscribe Newsletter Covering Trading Strategies, Risk Management, Financial Derivatives, Career Perspectives, and More
  1. Historical volatility is not responsive enough to the increase in volatility;
  2. When the market stabilizes, the past volatile period still stays in the sample and is assigned an equal weight as the most recent period;
  3. When the volatile period goes out of sample in the calculation of historical volatility, we suddenly experience a drop in volatility that is not caused by changing market fundamentals.

The picture below illustrates the above points. The blue line depicts the 3-month equally weighted historical volatility of SPY.  As we can observe from the figure, when the SP500 went down in early March of 2020, the implied volatility (not shown) went up, but the equally weighted volatility was lagging. It started going up only a week later. By mid-June, the market started stabilizing, but the equally weighted historical volatility still exhibited a high value. And finally, the equally weighted HV dropped abruptly at the beginning of July.

Exponentially Weighted Historical Volatility in Excel

To remedy these problems, one can use the Exponentially Weighted (EW) historical volatility that assigns bigger weights to the recent returns, and smaller weights to the past ones. The EW return variance is calculated as follows,

Volatility Analysis in Excel

where λ is a weighting factor,

         σt denotes volatility at time t,

         m is the sample size, and

        rt denotes the daily return at time t.

It is observed from the figure above that the EWHV (red line) is more responsive than the equally weighted historical volatility. Also, the decline of the EWHV from its peak is smoother than that of the equally weighted HV.

The equally and exponentially weighted historical volatilities were implemented in an Excel workbook.


To download the accompanying Excel workbook or Python program for this post:
1. Subscribe to the newsletter. If you're already a subscriber, go to the next step
2. Once subscribed, refer a friend
After completing these steps, you’ll gain access to the file for this post, along with files for a dozen other posts.

Also check out Historical Volatility Online Calculator.

Subscribe to newsletter https://harbourfrontquant.beehiiv.com/subscribe Newsletter Covering Trading Strategies, Risk Management, Financial Derivatives, Career Perspectives, and More

Further questions

What's your question? Ask it in the discussion forum

Have an answer to the questions below? Post it here or in the forum

LATEST NEWSInventronics Announces Option Grants
Inventronics Announces Option Grants

CALGARY, Alberta, May 20, 2025 (GLOBE NEWSWIRE) — Inventronics Limited (“Inventronics” or the “Corporation”) (IVX:TSX Venture) announces the grant of options to purchase common shares of the Corporation to members of the management team and Board of Directors of the Corporation. Each of the four…

Stay up-to-date with the latest news - click here
LATEST NEWSThrive Launches Compliance Center Offering Unrivaled Expertise
Thrive Launches Compliance Center Offering Unrivaled Expertise

Thrive Enables Organizations to Protect Data, Enhance Security, and Meet Regulatory Standards BOSTON, May 20, 2025 (GLOBE NEWSWIRE) — Thrive, a global technology outsourcing provider for cybersecurity, Cloud, and IT managed services, today announced its new Compliance Center. The Compliance Center provides mid-market businesses and…

Stay up-to-date with the latest news - click here
LATEST NEWSnCino Unveils Transformative AI-Powered Banking Solutions at nSight 2025
nCino Unveils Transformative AI-Powered Banking Solutions at nSight 2025

Key nCino Platform enhancements released to help banks, credit unions, and IMBs gain a competitive advantage through intelligence-driven automation Company launches nCino Research Institute to deliver economic insights and actionable strategies for banking growth WILMINGTON, N.C., May 20, 2025 (GLOBE NEWSWIRE) — nCino, Inc. (NASDAQ:…

Stay up-to-date with the latest news - click here
LATEST NEWSBirchtech to Exhibit at American Water Works Association Annual “ACE 25” Conference from June 8-11, 2025
Birchtech to Exhibit at American Water Works Association Annual “ACE 25” Conference from June 8-11, 2025

Company to Showcase Novel Water Treatment Solutions for Potable Water CORSICANA, Texas, May 20, 2025 (GLOBE NEWSWIRE) — Birchtech Corp. (TSX: BCHT) (OTCQB: BCHT) (“Birchtech” or the “Company”), a leader in specialty activated carbon technologies for sustainable air and water treatment, today announced management will…

Stay up-to-date with the latest news - click here
LATEST NEWSKP Tissue Announces Filing and Mailing of the Management Proxy Materials in Connection with its 2025 Annual Meeting of Shareholders
KP Tissue Announces Filing and Mailing of the Management Proxy Materials in Connection with its 2025 Annual Meeting of Shareholders

Your vote is important no matter how many shares you own. Vote today. Shareholders who have questions or need assistance with voting should contact KP Tissue’s proxy solicitation agent and shareholder communications advisor, Laurel Hill Advisory Group, by telephone at 1-877-452-7184 or by email at…

Stay up-to-date with the latest news - click here

Leave a Reply