Skip to content
All library documents

Generating Quarterly IMM Dates as the Third Wednesday in Excel

Article Quant Q&A · Author: Karusmeister
Read the original

Summary

The document defines IMM dates as the third Wednesday of March, June, September, and December, then gives an Excel date formula for generating the next quarterly date from a starting date. The formula advances three months, finds the first Wednesday in the target month using Excel’s weekday function, and adds two weeks to reach the third Wednesday. Dragging the formula down extends the sequence.

The method is useful for building or checking quarterly derivatives calendars in a spreadsheet. Its stated assumption is that the starting date falls in one of the IMM months; it advances by three calendar months from that date. The document provides an illustrative starting date and points to an adapted spreadsheet formula, but offers no testing across edge cases or discussion of date display settings. Users should ensure their starting month and spreadsheet date conventions match the intended quarterly schedule.

Key ideas

  • IMM dates fall on the third Wednesday of March, June, September, and December.
  • An Excel formula can locate the third Wednesday after advancing three months from a date.
  • Dragging the formula down produces a sequence of quarterly IMM dates.
  • The method assumes the initial date is in an IMM month.

Tags

FuturesOptions
Full text
# IMM dates in excel


# IMM dates in excel












I need to get the IMM dates in excel.

IMM dates are defined as the third Wednesday of every March/June/September/December.

## Answer by AP228 (score 2, accepted)

https://quant.stackexchange.com/a/8618

Not sure how you're looking to use it, but if you start with an IMM date in cell A1 (e.g. 9/18/2013), in cell A2 put...

=DATE(YEAR(A1), MONTH(A1) + 3, 1 + MOD(4 - WEEKDAY(DATE(YEAR(A1), MONTH(A1) + 3, 1)), 7) + 14)

...and drag it down. It will return the 3rd Wednesday of the month for every third month, so assuming that the month of the date you put in cell A1 is March, June, September, or December, this will give you what you're looking for.

I modified this answer here from another forum: http://www.mrexcel.com/forum/excel-questions/25668-calculating-every-third-friday.html

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.