How To Calculate The Next IMM Date

If you work with credit default swaps (CDS’s) it can be quite useful to have an Excel function which calculates the next IMM date from a given date. IMM dates for credit default swaps are unadjusted and fall on 20th March, June, September and December.

It’s not quite as easy to do this with native worksheet functions as you might first think, but this formula does the job quite nicely:

 =DATE(YEAR(A2),CEILING(MONTH(A2)+(DAY(A2)>20),3),20)

The trick to this formula is the CEILING() function which is being used to round the month portion up to the nearest multiple of significance of 3 – there are 3 months between each IMM date. If the date in cell A2 is an IMM date then the formula returns that date – if you’d prefer it to return the following IMM date then just change the > to a >= .

There are lots of different ways to do the same calculation in VBA. Here’s an interesting one:

Public Function NextIMMDate(ByVal dteFromDate As Date) As Date

    Const lngMONTHS_PER_ROLL As Long = 3
    Const lngROLL_DAY As Long = 20

    Dim lngMonth As Long

    lngMonth = -Int((-Month(dteFromDate) - IIf(Day(dteFromDate) > lngROLL_DAY, 1, 0)) _
                    / lngMONTHS_PER_ROLL) * lngMONTHS_PER_ROLL

    NextIMMDate = DateSerial(Year(dteFromDate), lngMonth, lngROLL_DAY)

End Function

The calculation method of the IMM month definitely warrants some explanatory comments. Int() always rounds negative numbers away from 0 so, by negating the input into Int()  and then also the final output, we are rounding the number up. Factoring this in with the rest of the IMM month calculation, we have replicated the logic of CEILING() in the formula we saw earlier.

VBA does not have its own, native RoundUp() or Ceiling() functions: yes, you could call Excel’s worksheet function versions via the Application.WorksheetFunction class, but the above approach is faster.

Here’s a little test in the Immediate Window to see if it gives the expected results:

?NextIMMDate(#1/10/2012#)
20/03/2012
?NextIMMDate(#3/20/2012#)
20/03/2012
?NextIMMDate(#6/12/2012#)
20/06/2012

Edit: If you want to calculate the next Futures IMM Date (ie 3rd Wednesday of March, June, September, December)  then please read the comments below.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , , , . Bookmark the permalink.

9 Responses to How To Calculate The Next IMM Date

  1. Jon von der Heyden says:

    Hi Col

    I suppose one could achieve the same effect using the \ divide operator instead of the Int()?

    Also, what plugin are you using to display your VBA?

    Do you know how it is some Date functions return a serial date (formatted) and others (especially the old analysis toolpak ones) return the long integer value?

    As always Colin, nice article. Nice tip on how to emulate Ceiling. Thanks :)

    Like

  2. Jon von der Heyden says:

    Thanks Colin. So to emulate would likely involve that timer method you once blogged. I think the user should just format the result. lol

    Like

  3. Colin Legg says:

    Excel changes the format to a date when the formula is parsed (not when it is calculated) so a safer bet would be to use the worksheet’s change event handler. Best of all (as you rightly said), let the user do it!

    Like

  4. Colin Legg says:

    If you want to calculate the next Futures IMM Date (ie 3rd Wednesday of March, June, September, December) then use this formula posted by Barry Houdini on SuperUser.com:

    =LOOKUP(A2,DATE(YEAR(A2),{0,3,6,9,12;3,6,9,12,15},22)-WEEKDAY(DATE(YEAR(A2),{0,3,6,9,12;3,6,9,12,15},4)))

    Note that if the date in A2 is an IMM Date then the formula returns the following IMM date rather than the date in A2.

    This formula is derived from the general rule that you can calculate the Nth occurrence of a given day of the week in a given month by using:

    =DATE(Y,M,1+7*Nth)-WEEKDAY(DATE(Y,M,8-DayOfWeek))

    Like

  5. Holger Fullriede says:

    Sorry, If I am little slow, but would you get the last IMM date before the date in cell A2, for example ?

    Like

  6. Colin Legg says:

    Hi Holger,

    To get the previous CDS IMM date you could use the FLOOR() function instead of CEILING() :

    =DATE(YEAR(A2),FLOOR(MONTH(A2)-(DAY(A2)<20),3),20)

    If the date in A2 is an IMM date then this formula will return that date. If, in that case, you still want to return the previous IMM date then change the < sign to <=

    Like

  7. Holger Fullriede says:

    Hi Colin,

    that seems to work just fine – thank you very much for the quick solution !

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s