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

B

barefootbroker

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating interest due on late loan payments 1
to get the same answer from a normal calculator 1
to get the same answer from a normal calculator 1
Financial Loan calc including monthly fees 6
The RATE worksheet function 0
APR Question 11
Loan Repayments Calculation 3
Is there a way for excel to recognise that €0.00 = 0 for the purposes IF function ? 4

Top