PDA

View Full Version : Financial Functions



Klartigue
06-03-2015, 01:10 PM
Hello,

On the attached sheet I have a 1st coupon date, today + 3 business days date, last coupon date, and frequency.

If the coupon frequency is 2, meaning semiannual, then the bond pays a coupon twice a year starting on the 1st coupon date and going until the last coupon date. If the today + 3 date is 6/8/2015, then the next coupon payment will be 7/1/2015 (this is based on the 1st coupon date of 1/1/14.. but obviously since 1/1/14, 7/1/14, 1/1/15 have passed already, the next one we would be concerned about would be the first one after today's date.

Is there a function in excel to put in cell A7 to read the data in column k and come up with the coupon payment dates in red in column A?

Sixthsense..
06-04-2015, 12:20 AM
In A7 Cell


=IF(EDATE($K$7,((12/$K$10)*ROWS($1:1))+(12/$K$10)*INT(DATEDIF($K$7,TODAY(),"M")/(12/$K$10)))<=$K$9,EDATE($K$7,((12/$K$10)*ROWS($1:1))+(12/$K$10)*INT(DATEDIF($K$7,TODAY(),"M")/(12/$K$10))),"")


Drag it down :)