Expiry Date Calculation for Equity Options


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:

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


  • 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(.). 

    Comment actions Permalink

Please sign in to leave a comment.

Was this article helpful?
0 out of 3 found this helpful