MarketXLS

Ex-Earnings Implied Volatility 1 Year

Returns the 1-year (12-month) implied volatility with the earnings event premium removed. This metric is essential for LEAPS analysis and long-term options strategies.

Why Ex-Earnings IV?

Options prices include extra premium when earnings announcements fall within the option's expiration window. This function removes that premium to show:

  • The "true" underlying volatility expectation
  • Better comparison across time periods (with and without earnings)
  • More accurate volatility for long-term investment strategies

Parameters

Parameter Required Description
Symbol Yes Stock ticker symbol (e.g., AAPL, TSLA)
StartDate No Historical date for IV lookup

Notes

  • Covers approximately four quarters of trading
  • Typically includes 4 earnings events for quarterly reporters
  • Essential for LEAPS pricing and long-term hedging strategies

Syntax

=ExEarningsImpliedVolatility1y(Symbol, [StartDate])
Excel Desktop (Windows)

Parameters

symbolstringRequired

Stock ticker symbol

startDatedate

Historical date for IV lookup (YYYY-MM-DD format or Excel DATE())

Returns

number

1-year implied volatility excluding earnings premium as a decimal

Examples

=ExEarningsImpliedVolatility1y("AAPL")
Current ex-earnings 1y IV
=ExEarningsImpliedVolatility1y("TSLA")
Tesla ex-earnings 1y IV
=ExEarningsImpliedVolatility1y("NVDA")
NVIDIA ex-earnings 1y IV
=ExEarningsImpliedVolatility1y("AAPL", DATE(2024,6,15))
Historical ex-earnings IV
=ExEarningsImpliedVolatility1y(A1)
Symbol from cell reference

When to Use

  • Analyzing LEAPS volatility expectations without earnings noise
  • Planning long-term options and hedging strategies
  • Comparing annual volatility levels across multiple earnings cycles
  • Identifying if elevated IV is due to earnings or secular trends
  • Long-term volatility term structure analysis

When NOT to Use

Scenario Use Instead
Need total IV including earnings ImpliedVolatility1y()
Need 6-month ex-earnings IV ExEarningsImpliedVolatility6m()
Need 90-day ex-earnings IV ExEarningsImpliedVolatility90d()
Need 60-day ex-earnings IV ExEarningsImpliedVolatility60d()
Need 30-day ex-earnings IV ExEarningsImpliedVolatility30d()

Common Issues & FAQ

What is earnings premium?

Earnings premium is the extra implied volatility priced into options when an earnings announcement is expected before expiration. Stocks can move significantly on earnings, so options reflecting this risk trade at higher IV.

How do I calculate the annual earnings premium?

Subtract ex-earnings IV from total IV:

How many earnings events are typically in 1 year?

For quarterly reporters, typically four earnings events. The ex-earnings calculation removes all expected earnings premiums within the window.

Related Formulas

More MarketXLS Options formulas you can use in the same worksheet:

See ExEarningsImpliedVolatility1y used in a complete workbook: MarketXLS New Release 9.3.4.7

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Ex-Earnings Implied Volatility 1 Year and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions