Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
APR Rate on a Loan | Excel Worksheet Functions | |||
How to total itmes if they fall between a date range | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) | |||
Variable rate loan amortization template | Excel Discussion (Misc queries) | |||
Variable rate loan amortization template | Excel Discussion (Misc queries) |