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:
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
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:
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.