date formula

[dennis]

hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis

 

[Chris Rogers]

you could use data validation to supply a limited number of dates to
choose from.

 

[Ron Rosenfeld]

hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis

What do you want to happen if the first payment date is 8/30/2005, or
3/31/2005?

If that is not an issue, the general formula, assuming your dates are in a
column with no blank rows, would be:

=DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate)
+6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate))

However, if for a First Payment Date of 8/30/2005 you want the next payment to
be due on the last day of February. and for 3/31/2005 you want the next payment
to be on the last day of September, then it becomes more complicated.

=IF(DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate)+6*ROW()
-ROW(FirstPmtDate)*6,DAY(FirstPmtDate)))=DAY(FirstPmtDate),DATE(
YEAR(FirstPmtDate),MONTH(FirstPmtDate)+6*ROW()-ROW(
FirstPmtDate)*6,DAY(FirstPmtDate)),DATE(YEAR(FirstPmtDate),
MONTH(FirstPmtDate)+6*ROW()-ROW(FirstPmtDate)*6,DAY(
FirstPmtDate))-DAY(DATE(YEAR(FirstPmtDate),MONTH(FirstPmtDate)
+6*ROW()-ROW(FirstPmtDate)*6,DAY(FirstPmtDate))))


--ron

 

[Ron Rosenfeld]

hi,

i've built a loan amortization schedule with payments due semi-annually. my
problem is that the payment must be on the same day of the 6th and 12th
month.
for example payments due 2/12/05 and 8/12/05 or 9/23/05 and 3/23/06. the
problem is that months have 30 or 31 days and then there's february and leap
years too. is there a way to program this function so that manual entry
payment dates can be eliminated?

tia
dennis

If you have installed (or can install) the Analysis Tool Pack, you can use a
simpler formula than the one I previously posted:

Again, with the payment dates in a column under FirstPmtDate and no blank rows:

=EDATE(FirstPmtDate,(ROW()-ROW(FirstPmtDate))*6)

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

 

[Fred Smith]

To get the next date 6 months out, use:

=date(year(a1),month(a1)+6,day(a1))

Excel is smart enough to roll over to the next year if you're starting in
months from Jul to Dec.

 

[Ron Rosenfeld]

To get the next date 6 months out, use:

=date(year(a1),month(a1)+6,day(a1))

Excel is smart enough to roll over to the next year if you're starting in
months from Jul to Dec.

Try 8/30/2005 in A1.


--ron

 

[Jim May]

ron, wouldn't there be a problem only if one were to begin their series with
the following 4:
8/29,
8/30,
8/31
or 2/29 ?

All others (limited test) seem to work..
TIA,

 

[Ron Rosenfeld]

ron, wouldn't there be a problem only if one were to begin their series with
the following 4:
8/29,
8/30,
8/31
or 2/29 ?

The problem will occur with the following dates:

29-Feb
31-Mar
31-May
29-Aug *unless subsequent year is a leap year
30-Aug
31-Aug
31-Oct
31-Dec


--ron