Excel PMT function returns incorrect values

[Guest]

I am trying to calculate the payment for a loan however the PMT function
returns only the interest payment and not the principle payment.

The following function =pmt(.06125/12, 3600, 63000) returns a value of 321.56
however when I check for the same value on all other mortgage calculators on
the web, the value I should be recieving is 382.79. after some
investigation, I have found that the pmt function is returning the interest
payment only for the first payment. The principle repayment amount which
should be 61.23 of is not returned.

The next thing I tried is the PPMT function and
=PPMT(.06125/12,1,3600,63000) returns a value of 0, as opposed to an expected
value of 61.23.

Has anyone else seen this problem? I checked the support site, and don't
see any patches that will fix this problem.

 

[Domenic]

That's because the number you're using for the total number of payments
for the loan is incorrect. The number should be 360, not 3600.

Formulas should be...

=PMT(0.06125/12,360,63000)

and

=PPMT(6.125%/12,1,360,63000)

Hope this helps!

 

[Myrna Larson]

Nice catch re the number of payments! 3600 payments is 300 years!

With 3600 payments, if you set up 3600 formulas for IPMT and PPMT and increase
the number of decimal places to 6 or more, you see that the first principal
payment is -$0.000003527. With the display set to 2 decimal places, it appears
to be 0 until you get to payment #1427.