Incorrect Results Using the PMT Function

[NormanRShapiro]

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!

[#2]

I'm no banker or even mathematician but I'm pretty sure your problem is using an average interest rate (even a weighted average) on a compounding interest calculation. Just looking at your example the 1% payment is $1,287 the 2% is $1,478 and the 3% is $1,686 so if this were linear (i.e. for a simple weighted average to work) then the difference from 1% to 2% should be the same as the difference from 2% to 3% but instead they are $191 and $208 respectively. Knowing mortgages and compounding interest you would expect this and why it is so good to get lower interest rates.

[#3 · Accepted solution]

@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.

[#4]

@Joe User 

Thank you very much!