ISMPT compared to Amortization

[Guest]

In Excel the ISMPT formula does not return the same results as an
amortization schedule. What is the differnce? The interest calculation for
the first payment on the example giving under Excel help is not the same as
the interest shown in an amortization schedule

 

[Fred Smith]

It will help if you give us an example of where you think Excel is going wrong.

What parameters did you use for ISPMT, what calculations for the amortization
table?

 

[Guest]

If you use the example in Microsoft Excel Help for first year annual
intersest on an $8 million loan with only one payment in the first year the
example says the interest is $533,333. In an amortization schedule, since
interest is @ 10% the annual interest would be $800,000 which seems like the
right answer as oppposed to the ISPMT calculation. What am I doing wrong?

 

[joeu2004]

If you use the example in Microsoft Excel Help for first year annual
intersest on an $8 million loan with only one payment in the first year the
example says the interest is $533,333. In an amortization schedule, since
interest is @ 10% the annual interest would be $800,000 which seems like the
right answer as oppposed to the ISPMT calculation. What am I doing wrong?

Nothing. You are correct. Use IPMT() instead of ISPMT(). But ignore
the IPMT() Help page examples. Geesh! It seems that Bill's Boys (and
girls) can never get it right.

First, note that ISPMT() is provided for Lotus compatiblity. Since I
do not have Lotus, I cannot confirm or deny that Excel's ISPMT()
(mis)behavior matches Lotus's. But if it does, the fault is with
Lotus, not Excel. I __can__ confirm that you are correct: ISPMT()
does __not__ return the correct answer, regardless of which example you
look at on the Help page. This is self-evident if you put the
following formula into A1, then copy down for the remain "nper-1" rows:
(a) for the first example, =ISPMT(10%/12, ROW(A1), 3*12, 8e6) ; (b)
for the second example, =ISPMT(10%, ROW(A1), 3, 8e6) . In both cases,
the interest in the last row is zero. Obviously that is wrong, if you
assume a normal reduction loan. Honestly, I have yet to figure out
what assumptions the ISPMT() results is based on.

If you repeat the same experiment using IPMT(), you will see a
difference. IPMT() is correct for a normal reduction loan. It should
match your amortization schedule. However, in the IPMT() Help page,
the first example's formula does not match the text; but at least the
second example's formula does. The first example's formula actually
computes the interest paid in the __last__ period, not the first
period. But that is just an accident of implementation. I am sure the
Help page author meant to write "A3, A4*12" instead of "A3*3, A4"; but
the expected answer (-22.41) does match the input parameters shown.

I am using Office Excel 2003 (11.6512.5606). I might not have the
latest patches.

 

[Guest]

Thanks so much. I am not crazy!!!

Nothing. You are correct. Use IPMT() instead of ISPMT(). But ignore
the IPMT() Help page examples. Geesh! It seems that Bill's Boys (and
girls) can never get it right.

First, note that ISPMT() is provided for Lotus compatiblity. Since I
do not have Lotus, I cannot confirm or deny that Excel's ISPMT()
(mis)behavior matches Lotus's. But if it does, the fault is with
Lotus, not Excel. I __can__ confirm that you are correct: ISPMT()
does __not__ return the correct answer, regardless of which example you
look at on the Help page. This is self-evident if you put the
following formula into A1, then copy down for the remain "nper-1" rows:
(a) for the first example, =ISPMT(10%/12, ROW(A1), 3*12, 8e6) ; (b)
for the second example, =ISPMT(10%, ROW(A1), 3, 8e6) . In both cases,
the interest in the last row is zero. Obviously that is wrong, if you
assume a normal reduction loan. Honestly, I have yet to figure out
what assumptions the ISPMT() results is based on.

If you repeat the same experiment using IPMT(), you will see a
difference. IPMT() is correct for a normal reduction loan. It should
match your amortization schedule. However, in the IPMT() Help page,
the first example's formula does not match the text; but at least the
second example's formula does. The first example's formula actually
computes the interest paid in the __last__ period, not the first
period. But that is just an accident of implementation. I am sure the
Help page author meant to write "A3, A4*12" instead of "A3*3, A4"; but
the expected answer (-22.41) does match the input parameters shown.

I am using Office Excel 2003 (11.6512.5606). I might not have the
latest patches.