Next Coupon Date: The Longest IF THEN Formula

This was actually a simple problem in semi-annual coupon bond pricing when I was asked to determine the next coupon/interest date after the settlement date. I just needed to use my common sense to find the date from the maturity date.



If the maturity date is 15-Jul-08 and the coupon is paid semi-annually, then the coupon dates are 15-Jan and 15-Jul (6 month difference) ignoring the year. If the settlement date is 16-Oct-07, then the next date after 16-Oct-07 between 15-Jul and 15-Jan should be 15-Jan-08. It's easy!

However, Excel and Visual Basic never consider my common sense to work just like that. There is always a formula or function need to be developed. Surprisingly, using the arithmetical date formula, I came up with the longest if then formula I've ever made. That's crazy as I couldn't find the simpler one.

Finally, I found this bloody Excel formula:

=IF(AND(B3>DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)),DATE(YEAR(B3),MONTH
(F3)+6,DAY(F3))>DATE(YEAR(B3),MONTH(F3),DAY(F3))),DATE(YEAR(B3)+1,
MONTH(F3),DAY(F3)),IF(AND(B3<DATE(YEAR(B3),MONTH(F3),DAY(F3)),
DATE(YEAR(B3),MONTH(F3),DAY(F3))<DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))),
DATE(YEAR(B3),MONTH(F3),DAY(F3)),DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))))

And this is the easy way of how you look at the formula:

IF(
AND(B3>DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)),
DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))>DATE(YEAR(B3),MONTH(F3),DAY(F3))),

=> if Settlement Date > first Coupon Date > second Coupon Date

DATE(YEAR(B3)+1,MONTH(F3),DAY(F3)),
=> next coupon date

IF(
AND(B3<DATE(YEAR(B3),MONTH(F3),DAY(F3)),
DATE(YEAR(B3),MONTH(F3),DAY(F3))<DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))),

=> if Settlement Date < first Coupon Date < second Coupon Date

DATE(YEAR(B3),MONTH(F3),DAY(F3)),
=> next coupon date

Else,
=> if first Coupon Date < Settlement Date < second Coupon Date

DATE(YEAR(B3),MONTH(F3)+6,DAY(F3))))
=> next coupon date

Bear in mind that 15-Jul-07 is the first coupon date and 15-Jan-08 is the second coupon date and the settlement date 16-Oct-07 is between both dates, therefore the next coupon date is DATE(YEAR(B3),MONTH(F3)+6,DAY(F3)), which is on 15-Jan-08.

If you want to be spoiled by Excel, there is actually a formula already provided: COUPNCD(settlement,maturity,frequency,basis).
I just wanted to be a dumb person who had been trying to figure out this IF THEN formula.