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
✅ Dosh NZ - Best Way To Pay: dosh.nz/app and use my referral code 00030886
✅ Get $5 FREE with Sharesies (NZ/AU): sharesies.com/r/C744KD
✅ Get $45 with DoorDash: drd.sh/yzemFEO8gYRTjiKt
✅ Free Month Car Insurance coveinsurance.co.nz/referred/...
✅ Get $10 FREE with Hatch Invest (NZ): app.hatchinvest.nz/share/hmwy...
✅ Hnry (NZ) Free $25 Freelance Accountant: app.hnry.io/r/BRENT82488
✅ Sharesight (NZ/AU/US/UK) 4 Months Free: sharesight.com.au/brentcoleman
// 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
KZbin 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! 🙏

[@jessmorris7202]

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!

[@Nanowolf100]

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

[@BrentColeman]

Glad it was helpful!

[@ninjatacoshark]

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

[@BrentColeman]

Glad it was helpful!

[@user-jb5pp9wl3b]

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

[@BrentColeman]

Thank you for your feedback! I appreciate you letting me know, and I'll make sure to take it into consideration for my future videos.

[@sonny_bhatti]

Amazing. Thank you

[@BrentColeman]

Glad it was useful!

[@WolfNnyl]

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

[@BrentColeman]

Thanks! Yes I do, I've been living here since 2019.

[@jalix99]

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?

[@BrentColeman]

Heya! Instalments come to about $495.29 if I've done it right. Mindful some banks calculate their loans slightly differently. Cheers!

[@1paradox]

@@BrentColemansince you didn't have a date field in your schedule, in this biweekly scenario how can I figure out how quickly I can pay off with extra payments? Like in YR2030 vs YR2040

[@BrentColeman]

@@1paradox Heya! You'd need to work out the number of instalments until the occurrence you'd wish to pay by. For example, if you wanted the debt to be fully paid by 2030, you'd take 26 yearly instalments (biweekly) multiplied by about 5.75 (i.e. 5 years and 9 months). In this case, you'd estimate there's about 150 instalments out to 2030. Put 5.75 as the Term (years) and 26 as the instalments per year. Give it a try and let me know how you get on :)

[@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

[@BrentColeman]

Heya! This kind of calculation would require the use of Excel's Goal Seek feature to insert the fixed instalments into a schedule and solve for the unknown instalment value to fully amortise the loan. It's too bespoke to cover in this forum unfortunately

[@lucifernjay]

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

[@BrentColeman]

Heya! You can simply use the PMT formula just the same, using 20 periods. The rate will need to be adjusted from monthly to weekly - this should give an approximation. Something like this: =pmt(((0.07*12)/365)*7,20,-loan amount,0,0). Give that a try!

[@lucifernjay]

@@BrentColeman it’s not work bro Loan amount 50,000 20 week instalment 6% interest per month And i need to get 3313 weekly instalment answer could you please help me bro

[@BrentColeman]

@@lucifernjay I am a bit confused. Nothing you've provided mathematically works. What kind of loan is this, the interest is extremely high and oddly charged monthly while your repayments are weekly? I don't think I can help sorry

[@lucifernjay]

@@BrentColeman it’s called MICRO FINANCE bro

[@heretogetupdated6199]

Are you single? 🙈👀

[@BrentColeman]

Getting married in 2 hours, so no 😂

[@Kbee945]

@heretogetupdated6199 you can also see his wife in the thumbnail of his “introduction to my channel” video posted 2 months ago