Next Expiry Date
Returns the option expiration date at a specified offset from the nearest expiration. This function is essential for dynamically building option chains and option symbols.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Symbol | Yes | Stock ticker symbol (e.g., AAPL, MSFT, SPY) |
| NumberOfExpiration | Yes | Offset from nearest expiry (1=next, 2=second, etc.) |
Understanding NumberOfExpiration
| Value | Description |
|---|---|
| 1 | Next upcoming expiration |
| 2 | Second upcoming expiration |
| 3 | Third upcoming expiration |
| 4 | Fourth upcoming expiration (often monthly) |
Expiration Types
| Type | Typical Schedule |
|---|---|
| Weekly | Every Friday (some stocks) |
| Monthly | Third Friday of month |
| Quarterly | End of quarter |
| LEAPS | January, 1-2 years out |
Common Use Cases
This function is typically used with OptionSymbol() and StrikeNext() to build dynamic option references:
=OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))Syntax
=ExpirationNext(Symbol, NumberOfExpiration)Parameters
The underlying stock ticker symbol
Offset from nearest expiration. 1=next expiry, 2=second expiry, etc.
Returns
Expiration date at the specified offset
Examples
=ExpirationNext("AAPL", 1)=ExpirationNext("AAPL", 2)=ExpirationNext("MSFT", 4)=ExpirationNext("SPY", 1)=OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))=TEXT(ExpirationNext("AAPL",1), "MMM DD, YYYY")=ExpirationNext("AAPL",1) - TODAY()=ExpirationNext(A1, B1)When to Use
- Find the next available expiration date
- Build dynamic option symbols
- Create option chains programmatically
- Calculate days to expiration
- Find specific expiration cycles (weekly, monthly)
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need all expiration dates | Expirations() |
| Need strike prices | StrikeNext() |
| Need option prices | Option_Last_Price() |
| Need full option chain | qm_option_chains() |
Common Issues & FAQ
What does numberOfExpiration=1 return?
It returns the next available expiration date after today.
How do I find monthly expirations vs weekly?
Use higher numbers (3, 4, 5+) to get monthly expirations. Weekly options typically have shorter-dated expirations.
Why doesn't my stock have weekly expirations?
Not all stocks have weekly options. Major stocks and ETFs (AAPL, MSFT, SPY) typically have weeklies.
How do I build a complete option symbol?
Combine functions: =OptionSymbol("AAPL", ExpirationNext("AAPL",1), "Call", StrikeNext("AAPL",1))
Excel Templates Using Next Expiry Date
These ready-made MarketXLS templates call ExpirationNext() in their worksheet formulas. Open one to see the function working inside a complete model.
- Template for Accessing Real-Time Data on US Stock Option Bids, Greeks, Open Interest, and Unusual Op
- Unusual Options Activity Scanner
- NDX100 Options Matrix: Comprehensive Risk-Weighted Analysis for Optimal Option Selection across All
- ^SPX Real time Option Chain with SPXW
- Option Chain Excel Sheet (SPY Example)
- Option Chain Matrix
- Real-Time Analysis: Options with Highest Open Interest, Change, and Volume
- Calculating Expected Stock Movement with MarketXLS: A Comprehensive Guide
Related Formulas
More MarketXLS Options formulas you can use in the same worksheet:
- OI Ratio (Options)
- OI Ratio (Specific Expiry) (Options)
- Open Interest
- Open Interest Historical
- Opt American Delta
- Opt American Gamma
- Opt American Implied Vol
- OPT AMERICAN PRICE
See ExpirationNext used in a complete workbook: When Do SPX Options Expire? Monthly vs Weekly
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today