Thanks for the reply OssieMac,

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) and one Tab where the PMT type = 0 (where PMT results is
correct)


"OssieMac" wrote:

Hi Tim,

Could you post the formula you are using to calculate the month by month
payment.
Also, have you checked the way you are applying your formula against the
Type parameter in PV? This makes a significant difference.

Regards,

OssieMac

"Tim" wrote:

I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV
amount in the function. I am trying to calculate a monthly loan payment
where the loan amount has a residual after the last payment. The values I am
using are as follows:

Rate = 1.5%
Nper = 24
PV = $120,000.00
FV = $37,000.00
Type = 1

The PMT function yields a $4,629.46 payment amount.
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

(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?