Excel Magic Trick 510: Amortization Table That Changes When Year Input Is Changed

[emailuznow]

So basically i want to be able to say to myself, if you make an additional payment of £100 a month, you will finish your loan of in say 20 years rather than 30 years and also your loan interest will also reduce. I dont want it to be fixed at £100 pm because sometimes i make a an extra payment and sometimes less

Thank you

[ExcelIsFun]

I got your e-mail and comments. I'll make a video in a few days...

[emailuznow]

cool

thank you

[emailuznow]

so each time i make an extra payment that month my number of years i have taken the loan out for reduces (because of the extra payments) and also my interest i would have paid over the 30 years also reduces.

How can i set up a formula so that i can track each time i make an extra payment, i want to be able to see exactly how much my total interest would be and exactly when my loan will finish.

[emailuznow]

Hi

Exellent tutorial.

How can i have a formula within this table to calculate additional payments

e.g

I have borrowed 100,000 over 30 years interest 5.9%

I occasionally make extra payments every month (not fixed payment, basically make a payment of however much i can afford that month).

[emailuznow]

Thank you

[emailuznow]

Fantastic video

Mike, can i use the formula you have provided in Excel Magic Trick 407: Amortization Table W Variable Rate to make it a variable Interest table?

[ExcelIsFun]

Yes, but you have to be careful what the loan contract says becasue in 407, the PMT updates as soon as the principal or rate changes which is usually not the case in the contact.