On Aug 13, 2:06 pm, Tim wrote:
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)