Forum Discussion
Incorrect Results Using the PMT Function
NormanRShapiro wrote: ``I found an anomaly. When using the average interest rate of the different products [...], the PMT function gives a different monthly payment than the total of the 3 separate monthly payments.``
The only "incorrect result" is your incorrect assumption that the "average interest rate" would amortize the combined loan to zero using the combined payments.
For the combined payments (without rounding), the correct annual rate is calculated in I5 by =12*RATE(I6,SUM(F8:H8),-I2), which is 2.02666523496371%, not 2%.
The mathematical explanation is beyond the scope of this forum; and it is probably TMI. In a nutshell, it is due to the nature of a geometric progression v. a linear progression.
This is demonstrated by the attached Excel file, which is summarized by the table below.
Column E shows the correct amortization for the combined payment. Note the different annual rate in E5.
Column F shows the incorrect amortization for the combined payment and the incorrect average annual rate of 2%. Note that the balance goes negative in the final months (F367 and F368).
Column G shows the correct amortization with an average annual rate of 2% and a different payment in G6.
FYI, the "residual" balance in row 369 demonstrates the infinitesimal anomalies that are common with binary (floating-point) arithmetic. They arise because most decimal fractions cannot be represented in binary. The point is: we cannot expect the balance to be exactly zero, even when the loan is amortized correctly with unrounded periodic payments.
NormanRShapiro wrote: ``I found an anomaly. When using the average interest rate of the different products [...], the PMT function gives a different monthly payment than the total of the 3 separate monthly payments.``
The only "incorrect result" is your incorrect assumption that the "average interest rate" would amortize the combined loan to zero using the combined payments.
For the combined payments (without rounding), the correct annual rate is calculated in I5 by =12*RATE(I6,SUM(F8:H8),-I2), which is 2.02666523496371%, not 2%.
The mathematical explanation is beyond the scope of this forum; and it is probably TMI. In a nutshell, it is due to the nature of a geometric progression v. a linear progression.
This is demonstrated by the attached Excel file, which is summarized by the table below.
Column E shows the correct amortization for the combined payment. Note the different annual rate in E5.
Column F shows the incorrect amortization for the combined payment and the incorrect average annual rate of 2%. Note that the balance goes negative in the final months (F367 and F368).
Column G shows the correct amortization with an average annual rate of 2% and a different payment in G6.
FYI, the "residual" balance in row 369 demonstrates the infinitesimal anomalies that are common with binary (floating-point) arithmetic. They arise because most decimal fractions cannot be represented in binary. The point is: we cannot expect the balance to be exactly zero, even when the loan is amortized correctly with unrounded periodic payments.
