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.
RAD Excel 
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 :)
LikeLike
Hi Jon,
I’m using the WordPress’ SourceCode tag.
http://en.support.wordpress.com/code/posting-source-code/
It doesn’t support highlighting VBA, so I’m using VB in the language parameter. On occasions, it can be quite frustrating to use – particularly if you need to edit your post. The main thing I have problems with is code indentation; I might start posting code without indents and suggest that readers copy it into the VBA IDE and then use Stephen Bullen’s smart indenter to get it looking right. Cue another blog post…!
Nice question on Excel’s behaviour of automatically formatting dates for some “date” worksheet functions and not others. I’ll have a think and try to come up with a decent answer.
Thanks (as always) for your comment.
LikeLike
Regarding the date formatting, here’s what Mr Govert (XL-DNA) says:
http://stackoverflow.com/questions/8229039/how-to-get-excel-udf-to-return-an-actual-datetime-instead-of-general-or-double
It’s worth noting that this gives a date format:
=TODAY()
but something like this doesn’t:
=IFERROR(TODAY(),TODAY())
LikeLike
Thanks Colin. So to emulate would likely involve that timer method you once blogged. I think the user should just format the result. lol
LikeLike
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!
LikeLike
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))LikeLike
Sorry, If I am little slow, but would you get the last IMM date before the date in cell A2, for example ?
LikeLike
Hi Holger,
To get the previous CDS IMM date you could use the
FLOOR()function instead ofCEILING():=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<=LikeLike
Hi Colin,
that seems to work just fine – thank you very much for the quick solution !
LikeLike
hi Colin,
By inference, here is the formula for prior IMM date:
=DATE(YEAR(A2),FLOOR(MONTH(A2)-1+(DAY(A2)>20),6),20)
Also changed the frequency to SA is it is now the convention for CDS.
LikeLike
Hello, It seems like that there is something wrong with the formula.=DATE(YEAR(A2);CEILING(MONTH(A2)+(DAY(A2)>20);3);20). For example, the date in A2 is 2020-12-10, then it shows that the next IMM date is 2020-12-20. However, this was a Saturday. Therefore,shouldn’t the date move forward to 2020-12-19?
LikeLike