The values I am using are as follows:

Rate = 1.5%

Nper = 24

PV = $120,000.00

FV = $37,000.00

Type = 1

[....]

(1) Why does the PMT function not calculate the proper monthly

amount with both a PV and FV amount in the function?

(2) Is there a fix, or work-around to get the correct figure?

I get the right answer, no matter what assumptions I make below.

The PMT function yields a $4,629.46 payment amount.

I presume that's a typo. I get 4629.26 when the month rate is 1.5%

and payment is "in advance" (type=1).

Alternatively, perhaps the monthly rate is between 1.500244% and

1.500256% (rounded), namely =rate(24,4629.46,-120000,37000).

When I calculate this out in a month-by-month table in Excel,

the last payment needs to be almost $500 lower that this

monthly payment amount, in order to result in a residual of $37,000

If a monthly payment amount of $4,612.39 is used in the month-by-month

table, the last payment needs to be only a few cents different n order to

result in a residual of $37,000

I cannot duplicate your numbers. Please post the structure and

formulas for your "month-by-month table" (aka amortization schedule).

This is how I would structure it (without frills):

B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly

payment]

C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest]

E1: 120000 [initial loan amount]

A2: =A1+1 [payment number]

B2: =B1

C2: =(E1-B2)*$C$1 [monthly interest]

D2: =E1-E2 [monthly principal]

E2: =E1+C2-B2 [monthly ending balance]

Copy A2:E2 down through A25:E25.

Notes:

1. Technically, B1 should be =roundup(pmt(...),2). Then:

B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1)

E2: =max(0,E1+C2-B2)

Thus, the last payment will likely be less than the other monthly

payments.

2. Arguably, monthly interest might also be rounded (up?). That

depends on the lender. Obviously, the lender rounds (up?) the amount

of total interest reported on the 1099-INT at the end of year (for US

loans). But that does not necessarily dictate the lender's internal

computations.

3. Are you sure that the payment is "in advance", not "in

arrears" (type=0 or omitted)? For most loans that I've seen, payment

is "in arrears", with "prepaid interest" added to the closing cost to

cover the short (irregular) period at the beginning. In that case,

interest (C2) is =E1*$C$1, copied down. But that does not result in

the unusual numbers that you post ("almost $500" and $4,612.39)