Creating a Loan Amortization Schedule in Google Sheets | Easy Step-by-Step Guide

This video will show you how to create a simple loan amortisation schedule in Google Sheets, with minimal effort needed! As long as you know the loan amount, the term of the loan, and the interest rate, you can create the full breakdown quickly and easily.
// 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
// TIMESTAMPS
► 0:00 Intro
► 0:24 Creating the Schedule
► 6:10 2nd Example - Switching Monthly to Weekly Repayments
► 6:40 Concluding Remarks
► 7:08 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

[@andrewbalazs7422]

refreshingly straightforward yet very educational youtube video

[@BrentColeman]

Glad you found it useful!

[@user-ot3ht9er8h]

This was so helpful thank you

[@BrentColeman]

Glad it was useful!

[@jteichma]

Great video! Appreciate it.

[@BrentColeman]

Glad it was helpful!

[@EmpeyRealtySanDiego]

This is amazing, thank you!!

[@BrentColeman]

Glad it was useful!

[@Premiumcribs]

thank you so much for this well detailed video

[@BrentColeman]

Appreciate the comment - glad you enjoyed it!

[@gregorymalachilittle681]

Why is "present_value" for the IPMT formula not B9 then carried down since the balance is decreasing? I know the way you show is correct, I'm just trying to understand it better. Thanks!

[@victoriab.3203]

Thank you

[@BrentColeman]

Glad you enjoyed it!

[@junjunreynoso86]

Good video. Could you do the solar power providers, pros and cons? Thanks

[@BrentColeman]

Thanks for watching! Do you mean a comparison of companies selling solar power hardware?

[@chellelaclare6748]

Thank you for this, it's wonderful! BUT I'm trying to figure out what I did wrong here? The rates show up properly descending in interest and ascending in principal amounts as you go down the columns, and ends with the final payment being almost all principal, just $3.72 in interest in the final payment, BUT the Principal column stays the same down the entire column, it does not descend in value as you go down the column, it stays at $79,200 all the way down. :-/ I'm having to type in the dollar signs instead of F4, perhaps I wasn't supposed to somewhere along the line? In one place (interest field A10) you said NOT to lock it for one of the values, so I tried it both with and without the dollar signs (left it without them, as shown on yours) and it didn't seem to make a difference. Yikes, I’m pretty sure I did exactly what you said, but I seem to have messed up somewhere. Any help is greatly appreciated! (Wish I could attach a screenshot!)

[@Alacarta-ji4vn]

I've seen car loans with fixed anual payments on top of the installments to lower the installment payments, how do you calculate the installments for this types of loans?

[@BrentColeman]

Heya! I made a video a while back that explored making additional principal payments on top of the schedule, to reduce the loan term itself. To lower the repayments, this would be a restructured loan in New Zealand that often incurs a small fee. kzbin.info/www/bejne/fXO4XnShjL2rgpo Alternatively, you could use this calculator and check at monthly installment #12 what the principal is at that point. You could then reinput the loan details into the calculator, however with a term of (n-12), and repeat this for the number of years the loan will run. Each time you rerun the numbers you'll see how the repayments would change. Hope this helps!

[@patriciabomar8059]

What button are you referring to at the 4:01 mark in cell C10? I cannot understand what you're using to lock the formula?

[@BrentColeman]

Heya! I am pressing the F4 key, which locks cells in Excel/Sheets. Here is a link explaining more: edu.gcfglobal.org/en/excel-tips/absolute-references-with-the-f4-key/1/ . Hope this helps!

[@nathaniel7238]

I'm using my phone, so there are no hot keys. Just adding the $ as it appears in the video works though

[@chellelaclare6748]

@@BrentColeman Hello and thank you! But I have the same question, with a twist! I am using a chromebook and there is no F4 key. Help?

[@BrentColeman]

Heya! Just put dollar signs in front of the letter and number eg cell A1 can be locked by writing $A$1 without the need for the F4 key :)

[@chellelaclare6748]

@@BrentColeman You da best! 🙂