CD Dates

[mjddt35]

Hello,

I have a starting issued date and I need a formula that will keep changing to the next 6 month period based on the issue date until maturity, where the date will stop changing. The payments are made every 6 months (paid on Friday if interest falls on a weekend, but still paid through the day prior to the 6 month date). Example below.

Issued on 6/13/14 and paid semi-annually
The next interest date is 12/13/14 (Saturday) but will be paid on 12/12/14 (should reflect in the cell), but paid through 12/12/14. Once the today's date changes to 12/13/14 (day after the paid on date), the value then becomes 1/13/15 and paid through 1/12/15, and so on.

Any help would be greatly appreciated!

 

[CalcSux78]

I follow the first part, but I'm lost on the second part.

Excel 2010
	A	B	C
1	Issue Date	Payment Due	Paid on
2	6/13/2014	12/13/2014	12/12/2014
Sheet1

Cell Formulas
Range	Formula
B2	=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))
C2	=IF(WEEKDAY(B2)=7,B2-1,IF(WEEKDAY(B2)=1,B2-2,B2))

 

[mjddt35]

That works for the first pay date, but what about for the pay dates in the future? Once 12/13/14 arrives (the 12/12 amount has now been paid), the cell should then show the next 6 month pay date.

 

[CalcSux78]

We need to calculate how many months to add to the start date. Start with:

=(YEAR(NOW())-YEAR(A2))*12+MONTH(NOW())-MONTH(A2)

With this, we can divide by our period (6 mo), roundup to the nearest integer and multiply by 6 to give us the next payment date. The paid on formula shouldn't need any changes.

Excel 2010
	A	B	C
1	Issue Date	Payment Due	Paid on
2	6/13/2014	6/13/2016	6/13/2016
Sheet1

Cell Formulas
Range	Formula
B2	=DATE(YEAR(A2),MONTH(A2)+ROUNDUP(((YEAR(NOW())-YEAR(A2))*12+MONTH(NOW())-MONTH(A2))/6,0)*6,DAY(A2))
C2	=IF(WEEKDAY(B2)=7,B2-1,IF(WEEKDAY(B2)=1,B2-2,B2))

An alternative way to get paid on date is to alter the weekday formula to make Saturday =1, Sunday = 2 and you would use:
=IF(WEEKDAY(B2,16)>2,B2,B2-WEEKDAY(B2,16))