It was supposed to be a photograph taken by my coworker, but she took a wrong setting in the camera and it turned out to be a movie.
What a long "cheese", isn't it?
"...it's saying recording..."
supposed to be a photo: a narcist show
Next Coupon Date: 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.
Dirgahayu RI - Djakarta is Coming
Nothing particular, just wanna show how strong our country is therefore some intelligence uses those words to threaten a status quo.
"Dirgahayu Republik Indonesia", hopefully "Djakarta is not coming yet".
note: I got the words after watching "Spooks". Try the games.
