"Sandy Schmid" wrote:
I want to compare payments for a loan where the payment is
calculated at the end of the month vs. at the beginning of the
month. Excel Help says to change "type" from "0" to "1" to
change from arrears calculation to advance payment.

Did you mean "beginning and end of the period", not
"beginning and end of the month"? The latter sounds like
"calendar month". If you did indeed mean that, what would
you call a payment due on the 15th of a month? (Rhetorical.)

Normally, payments begin one month after the initial loan.
Interest is computed based on the balance at the beginning
of the period, before payment. That is payment "in arrears"
-- end of the period. It does not matter whether the period
ends on the 1st, 15th or last day of a calendar month.

Rarely, the first payment is due when the initial loan is
distributed. Subsequent payments begin one month later.
Interest is computed the same way; but there is no interest
for the first payment, since no time elapsed. That is
payment "in advance" -- beginning of the period.

(There is a third case where payments are due on some
date, the first of which is less than a month after the initial
loan. That is payment "in arrears", but the first payment
is for a shorter "odd" period, and interest is prorated for
the shorter period. For US mortgages, normally the "odd"
period is handled by including the interest for the short
period in the closing costs.)

You can see the effect of these 2 payment schedules as
follows:

1. =pmt(1%,12,-12000): $1,066.19 (in arrears)
2. =pmt(1%,12,-12000,,1): $1,055.63 (in advance)
3. =pmt(1%,11,-(12000-1055.63)): $1,055.63 (in arrears)

Note that the payment amount in advance (#2) is the same
as the payment amount in arrears (#3) for one less period
and an initial balance reduced by the payment amount.