Jun 02 2021 11:56 PM
Jun 02 2021 11:56 PM
I've attached a file which I made to help calculate mortgage options (I'm a mortgage broker) and show an amortization chart. It is used to calculate a mortgage package using different mortgage products.
I thought it was working just fine, until I found an anomaly. When using the average interest rate of the different products (when the interest rates are different - as they usually are), the PMT function gives a different monthly payment than the total of the 3 separate monthly payments.
It's driving me batty. Please help!
Jun 03 2021 07:23 AM
Jun 03 2021 07:36 AM - edited Jun 03 2021 07:43 AMSolution
@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.