Next coupon date - IF THEN formula

Apr 5, 2008
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.

Labels:



Comments:

Post a Comment

<< Home

Dutch Indonesian English
    I receive donation for premium financial model and website developments. Please contact me for any inquiries.
this tool only works in IE
Calculate Your Power
Your Energy
Your Age Now y.o.
Your after-tax Annual Income
Monthly Expenses
Desired Real Return %
Desired Retirement Age y.o.
Transcendence Ratio %
Your Power
Annual Saving
Power in 5 yrs
Power in 10 yrs
Power @ Retirement
Estate Planning
Charity
>> read Transcendence Power