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

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.

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.