PMT function in Excel
There is no error in the PMT function. When I build the amortization table, I
get exactly $37,000 at the end of 24 months. The most likely sources of error
a
1. You aren't replicating the type function properly. A type of 1 means payments
at the beginning of the month. You need to calculate the interest as =(OpenBal -
Pmt) * 1.5%
2. Rounding or typos. PMT returns $4,629.26 (not 4,629.46) with the parameters
given. And, don't round this number, otherwise you'll get small errors at the
end.
--
Regards,
Fred
"Tim" wrote in message
...
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?
|