Expiry Date Calculation for Equity Options

Mohamad

November 30, 2012 21:19

Question:

How can I use NumXL to compute the expiry date of a given equity option?

In the US, the equity option contract expires (stop trading) on the 3rd Friday of the expiry month.

To compute the expiry date:

Using the NxNWkDay function, compute the calendar date of the third (3rd) Friday of the contract delivery month.
Using the NxAdjust function, adjust the returned date in (1) using the OBOE calendar and using "Following" for the business day convention.

Mohamad
August 18, 2021 15:32
Since 2010, the option contract symbols (e.g., INTC210820C00027500) have all information encoded:
- Root symbol of the underlying stock or ETF, padded with spaces to 6 characters
- Expiration date, 6 digits in the format "yymmdd"
- Option type, either P or C, for put or call
- Strike price, as the price x 1000, front padded with 0s to 8 digits
For instance, consider the "INTC 210820C00027500" contract:
- Underlying stock: INTC
- Expiration date 210820: Year=2021, Month=08, Day= 20 (3rd Friday)
- Option type: C = Call
- Strike = $27.50
To extract information, we can use excel built-in function - substring(.).
0

Comment actions Permalink

Please sign in to leave a comment.