Find the Last Pay Date

[NumberGuy]

I'm tracking some bank CDs that mature in several years. They pay interest on a semi-annual basis, every six months from the purchase date. I need a formula that will tell me the last time interest was paid, based on today's date. For example:

Purchase Date: 10/26/10
Today's Date: 3/31/12

In this case, interest would have been paid on 4/26/11 and 10/26/11, so the correct answer is 10/26/11. If interest has not yet been paid (purchased less than six months ago), the answer should be the Purchase Date.

The formula should also be able to handle quarterly payments (every three months from purchase date). Any help is greatly appreciated!

 

[tigeravatar]

NumberGuy,

With the Purchase Date in A1:
=DATE(YEAR(A1),MONTH(A1)+INT(DATEDIF(A1,TODAY(),"m")/6)*6,DAY(A1))

Change the 6's to 3's for quarterly.

 

[barry houdini]

Hello NumberGuy, welcome to MrExcel

tigeravatar's suggestion looks good to me - this formula should also return the same result

=COUPPCD(TODAY(),EDATE(A1,400*12),2)

change the 2 at the end to a 4 for quarterly payments