Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISMPT compared to Amortization
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISMPT compared to Amortization
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? -- Regards, Fred "Lenderrs" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISMPT compared to Amortization
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? "Lenderrs" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISMPT compared to Amortization
Lenderrs wrote:
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ISMPT compared to Amortization
Thanks so much. I am not crazy!!!
" wrote: Lenderrs wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loan Amortization calculater (Canadian version) | Excel Discussion (Misc queries) | |||
Can Excel create an amortization schedule? | Excel Discussion (Misc queries) | |||
Change Xcel Amortization from 30 years to 40 years? | Excel Discussion (Misc queries) | |||
ARM mortgage amortization schedule | Excel Discussion (Misc queries) | |||
does anyone have a great Interest only amortization schedule wher. | Excel Discussion (Misc queries) |