Errata....

On Jul 29, 1:50 am, I wrote:
C4: =C2

Obvious typo: that should be =C3, not =C2.


B4: =date(year(A3), 1+month(A3), day(A3))

If you installed the Analysis ToolPak, a more reliable formula would
be:

B4: =date(year(A3), 1+month(A3), if(or(day($A$3)=eomonth($A$3,0)),
day($A$3)eomonth(A3,1)), eomonth(A3,1), day($A$3)))

Thus, if the first payment is at the end of the month (e.g. Feb 28),
every payment will be at the end of its respective month (e.g. Mar 31
and Apr 30 instead of Mar 28 and Apr 28). The same is true if the
first payment is on the 29th or 30th of the month and the current
month (Feb) has fewer days.

Alternatively, you can get the same result simply by filling in the
first 2 or 3 dates, selecting those cells and dragging down through
row 362. Excel tries to interpret the pattern.


D3: =if(or(A3=360, roundup(G2+E3,2) < roundup(pmt(C3/12,360-A3+1,-G2),
2)), roundup(G2+E3,2), roundup(pmt(C3/12,360-A3+1,-G2),2))

This is a nitpick, but: on second thought, I prefer to remove the
first "roundup" call, thus:

D3: =if(or(A3=360, G2+E3 < roundup(pmt(C3/12,360-A3+1,-G2),2)),
roundup(G2+E3,2), roundup(pmt(C3/12,360-A3+1,-G2),2))