The Easiest Loan Amortization Schedule With Extra Payments

In this video I provide a clear guide on how to create a loan amortization schedule in Google Sheets and Microsoft Excel. This can be useful to break down each installment on a mortgage, a car loan, a student loan, or even a personal loan - any loan as long as it has a fixed term and interest rate. We can then make smarter financial decisions by structuring our loan better, and making strategic lump sum repayments to pay it off quicker, and, pay less interest. The overall interest paid will reduce in the case you reduce the loan term, increase the installments per term (more compounding), and making additional repayments.

The loan schedule I create has a few columns, namely the loan balance, the installment amount, the P&I (principal and interest) split of each installment, and the option to add additional payments.

// SOCIAL
🌏 Follow Me On Instagram @brentcolemaninvesting

// USEFUL LINKS

// TIMESTAMPS
► 0:00 Intro
► 0:38 Create the schedule
► 6:36 Add the extra payment
► 8:43 Adapt the model to accept different variables
► 9:35 Outro

Disclaimer:
I am not a financial adviser. This video is for education and entertainment purposes only. Seek professional help before making any investment decision.

Song: Skylines - Anno Domini Beats
YouTube Studio Copyright Free Music

[BrentColeman]

Hey guys! Apologies, I spotted a mistake in the IF statement in cell C8. It should not have the extra payment in the IF TRUE section of the formula. It should instead be =if(pmt($B$2/$B$4, $B$3*$B$4, -$B$1, 0, 0)+F8-E8>B7, B7+E8, pmt($B$2/$B$4, $B$3*$B$4, -$B$1, 0, 0)+F8) . Sorry for any inconvenience! 🙏

[jessmorris]

This video was so helpful, thank you!
I was still able to follow your instructions while changing the instalments to 26 (fortnightly). I really appreciated your explanation of each step too!

[ninjatacoshark]

Awesome video, thanks for the help. Also huge props for going through each function and why certain cells need to be locked.

[Nanowolf]

Thanks for this video it really managed to go over everything even down to the intrest saved!

[WolfNnyl]

Thank you for this info mate. Btw are you from Auckland as well?

[joemygawd]

Any chance you could just share this spreadsheet to make a copy?

[CynthiaTaylor-gy]

Goodness I'm just trying to get the formulas and its TOO FAST for me to follow. Slow it down, please.

[natalief]

How do you add an option to have the variable loan rate change along the way. E.g. month 7, interest rate drops.

[jalix]

Hi, had a question about how to make this table work for a mortgage loan, 200k principle, bi-weekly payments at 5% int rate. Would you just enter those values and make the term 30 and then installments to 26?

[nokoemyim]

How to set Year 1 pay only interest and year 2 pay 100% interest plus Principal 10% and year 3 onwards pay full installment

[lucifernjay]

Wow. Can you give me example for weekly payment loan 20 weeks installments and 7% interest per month