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! ;-)
