Historical Theta (Options)
Returns the theta Greek for an option contract on a specific historical date. Theta measures the daily time decay - how much value the option loses each day as it approaches expiration.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Symbol | Yes | Option symbol (OCC format) |
| OnDate | Yes | Historical date (DATE function or string) |
Input Requirements
Use OptionSymbol() to generate the option symbol:
| Parameter | Source | Example |
|---|---|---|
| Symbol | OptionSymbol() output |
OptionSymbol("AAPL",DATE(2026,3,15),"Call",170) |
Understanding Theta
| Characteristic | Description |
|---|---|
| Usually negative | Options lose value over time |
| Accelerates near expiry | Theta increases as expiration approaches |
| Highest at ATM | ATM options have highest time decay |
| Per calendar day | Daily value loss from time passing |
Notes
- Theta is typically expressed as daily decay
- Negative theta means the option loses value each day
- Time decay accelerates in the final weeks before expiration
Syntax
=opt_ThetaHistorical(Symbol, OnDate)Examples
Using OptionSymbol() - RECOMMENDED
=opt_ThetaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Call",170),DATE(2025,12,15))Using raw OCC symbol
=opt_ThetaHistorical("AAPL240315C00170000", DATE(2025,12,15))Put option theta
=opt_ThetaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Put",170),DATE(2025,12,15))Using cell references
=opt_ThetaHistorical(A1, B1)Calculate weekly time decay
=opt_ThetaHistorical("AAPL240315C00170000", DATE(2025,12,15)) * 7 * 100When to Use
- Backtest time decay strategies
- Analyze how theta evolved over time
- Study theta acceleration near expiration
- Calculate historical position Greeks
- Analyze income from selling options
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need current theta | opt_Theta() |
| Need historical delta | opt_DeltaHistorical() |
| Need historical gamma | opt_GammaHistorical() |
| Need all Greeks at once | opt_HistoricalOptionChain() |
Common Issues & FAQ
Why is theta negative?
Theta is negative for long options because they lose value over time. This is the cost of holding the option.
Why did theta become more negative near expiration?
Time decay accelerates as expiration approaches. This is especially pronounced for at-the-money options in the final 2-3 weeks.
How do I calculate weekly theta decay?
Multiply daily theta by 7: =opt_ThetaHistorical(symbol,date) * 7. For a position, also multiply by contracts * 100.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?