On Aug 15, 8:04 am, Tim wrote:
Attached is a link to my Excel file which contains the PMT
formula, and the table I used to verify the formula.
http://www7.sendthisfile.com/d.jsp?t...kcARsCjkUKJjV8

In this Excel file you will find 2 Tabs, one Tab where the PMT
Type = 1 (& PMT result is wrong)

The mistake in the Type=1 worksheet is that you are not computing
interest correctly. For example, you have zero interest in the first
period. I used to do the same thing. But in fact, interest is
computed based on the previous balance (initial loan amount) less the
payment, as indicated for C2 in my previous posting. The proof of
this can be demonstrated with the following formula:

=fv(1.5%, 1, 4629.26, -120000, 1)

That computes the outstanding balance after the first payment "in
advance". Note that the result is 117,101.30 (rounded), not
115,370.74 (120000 - 4629.26).

As I noted in my previously posting (adapted to your worksheet),
interest should be computed as follows in C24 and copied down through
C47:

C24: =(E23-D24)*$C$11

(Also note that the payment is indeed 4629.26, rounded, in your
worksheet, not 4629.46 as you wrote in your initial posting.)

PS: The annual interest rate is 18% (12*1.5%), not 19.5620%, which
you compute in C12 by (1+1.5%)^12-1. The latter computes the APY of
an investment, not the APR of a loan.

and one Tab where the PMT type = 0 (where PMT results is correct)

Whether the payment is "in advance" or "in arrears" depends on the
terms of the loan. In both cases, the financial math should yield the
correct results.