loan payment with extra payments and variable interest

[darich]

Hi all
This is my first post and something I’ve been trying to do for a while now.

Let’s say I have a loan of £100k payable at 5% for 25 years.
I can do that no problem. PMT function is quite simple.

I can also work out how much I would save if I paid extra money into the loan. The balance reduces so the interest is lower for the remaining term. Again, not too complicated.

But the problem I have is this – what about when the interest on the loan changes?
It’s 5% for 25 years and I have the payments already. However if the rate changes to say 8% then the payments are calculated by using 8% of the original amount – not the amount outstanding at the time of the rate change ie the new payment is 8% calculated on 100k not on what is outstanding which would be less.

That means the date of the final payment changes and knocks the whole thing out. An increase in the rate means it’s not paid by the original final payment date and a reduction means it’s paid early. But in both cases that’s not true.

I’d also like to be able to calculate how much I’d save by making overpayments but not a constant overpayment – it’ll vary from 0 to a few hundred each month throughout the term.

Is it possible to make up a sheet that allows for the changing interest rate and varying additional payments, even using multiple tabs or worksheets?

I'm using Excel 2003.

Thanks!!

 

[Hexagon]

Hope I understood your problem correctly, here is my possible solution

TblA
1. say in cell A1 you type in a rate of interest
2. Say in cell A2 you place the principal loan amount (at inception)
3. MAtuyrity dAte
4. Etc.

then use the PMT() formular and insted of placing a fixed value in the criterias, you guide them to (for Interest to cell A1) , then for the PV to A2 etc.

you create a secend shcedule (call it Tbl B) dooing exactly the same, but say in cells starting d1

then all you do is change the interst value in cell D1 and change the rate of interest in columns G you calcualte the delta betweenTable A and table B