When amortising a loan based using an IRR rate, the total interestpaid fall short.
 

Hello,

I need to calculate an effective interest rate for a customer for a
loan that is receiving regular, additional deposits that are not paid
by the customer.

The customers repayment is calculated using xls PMT function at a
retail interest rate. The extra deposits reduce the term/nper and the
total interest paid by the customer below the retail rate to a lower,
effective interest rate and it is the lower effective rate is that I
am attempting to derive.

I have used =IRR(J22:J382,-M12/12)*12 to calculate the effective p.a.,
interest rate for monthly cashflows and the answer is within the range
I would expect it to be.

However when I apply the IRR to calculate the actual interest paid
using xls PMT function, i.e. =(PMT(N21/12,J21,J22)*J21)+J22 the actual
interest paid is lower than it is when summed manually.

I have tested each of these formulas using a loan amortization
schedule that does NOT involve the extra repayments and the results
contra, so I have misunderstood the handling of the extra payments,

Does anyone have any suggestions as to where I am going wrong?

Thanks,

Mick