pPMT malfunction

[Guest]

I amortizing a loan on an Actual/360 basis. I calcuated the payment using the
pmt function [eg. =pmt(.07/360*31,120,-1000000)]. I then added the results
from the the ipmt and ppmt functions for the first period (month 1) but they
did not total the results from the pmt function. As check to determine if
either ipmt or ppmt were correct I calculated the interest on the loan by
hand and was able to confirm that ipmt was correct. Thus ppmt was incorrect.
Any suggestions?

 

[Guest]

I amortizing a loan on an Actual/360 basis.

I quibble with your method. But that's another story.

I calcuated the payment using the pmt function [eg.
=pmt(.07/360*31,120,-1000000)]. I then added the results
from the the ipmt and ppmt functions for the first period
(month 1) but they did not total the results from the pmt
function. As check to determine if either ipmt or ppmt were
correct I calculated the interest on the loan by hand and
was able to confirm that ipmt was correct. Thus ppmt was
incorrect. Any suggestions?

Worked just fine for me. I suspect a data entry error. Whenever
you have a question like this, it is always helpful to post (a) the
Excel version number, and (b) the exact results that you get.
Sometimes, the problem is a data entry error. But a very small
difference might suggest an error due to format or option settings
or explicit rounding.

Using Office Excel 2003, I get the following results:

=PMT(7%/360*31,120,-1000000): $11,731.46
=IPMT(7%/360*31,1,120,-1000000): $6,027.78
=PPMT(7%/360*31,1,120,-1000000): $5,703.68
=SUM(...): $11,731.36