What is the formula to get a consecutive monthly date within a loan
amortization table? For instance, a monthly loan payment is due on
the 5th of each month. I'm trying to get excel to populate the next
cell down so I don't have to enter five or ten years worth of dates.

Thanks,

DonL

On Mar 22, 10:16*am, DonL <d...@steelelarson.com> wrote:
> What is the formula to get a consecutive monthly date within
> a loan amortization table? *For instance, a monthly loan
> payment is due on the 5th of each month. *I'm trying to get
> excel to populate the next cell down so I don't have to enter
> five or ten years worth of dates.

Depends on what you want in certain instances, and what restrictions
you might have (e.g. avoid the ATP in XL2003).

If A2 contains the date of the first payment and all rows of the
amortization table are contiguous (no blank lines or lines for year-
end totals), then ostensibly put the following into A3 and copy down:

=EDATE(A2,1)

or

=DATE(YEAR(A2),1+MONTH(A2),DAY(A2))

formatted as Date.

In XL2003, EDATE requires that the ATP be installed. Usually not a
big deal; but some users cannot or do want to rely on the ATP.

But the DATE formula is not identical to EDATE in when handling days
near the end of the month, i.e. Feb 28 and 29, and the 30th and 31st
of other months. Experiment to see which comes closer to your
requirements.

The problem with both is: if the date in A2 is a day of the month
that does not exist in some future month, the due date tends to
evolve. The following is more reliable:

=EDATE($A$2,ROW()-ROW($A$2))

or

=DATE(YEAR($A$2),ROW()-ROW($A$2)+MONTH($A$2),DAY($A$2))

formatted as Date.

But a problem with all of those: the future date might not be a
weekday. Generally, when that happens, the actual due date is the
next weekday. If you want to correct for that, then use the
following:

=EDATE($A$2,ROW()-ROW($A$2))
+IF(WEEKDAY(EDATE($A$2,ROW()-ROW($A$2)),2)<=5, 0,
8-WEEKDAY(EDATE($A$2,ROW()-ROW($A$2)),2))

formatted as Date.

Needless to say, it would be better to put EDATE($A$2,ROW()-ROW($A$2))
into a helper cell and reference it in the formula above.

Since WEEKDAY is in the ATP in XL2003, there is no point in trying to
provide a solution that does not use EDATE.

Last problem: even the last formula does not account for bank
holidays. I give up! ;-)

DonL wrote :
> What is the formula to get a consecutive monthly date within a loan
> amortization table? For instance, a monthly loan payment is due on
> the 5th of each month. I'm trying to get excel to populate the next
> cell down so I don't have to enter five or ten years worth of dates.
>
> Thanks,
>
> DonL

Have you looked at the amortization template that ships with Excel?
This would be in the 'Templates' folder and is available from the sheet
tab right-click 'Insert...' menu. This template automatically fills in
payment periods for the entire term of the loan.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

For payments falling last day of months, try this too:
B1=If(MONTH(A2)<>MONTH(A2+1),"YES","NO")
B2="YES"
If($B$1=$B$2, EDATE($A$2+1,ROW()-ROW($A$2))-1, EDATE($A$2,ROW()-ROW($A$2)))

Torsten Junge