Creating a Loan Amortization Schedule in Google Sheets

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

Рет қаралды 8,024

Brent Coleman

Күн бұрын

Пікірлер: 40

@andrewbalazs7422 Жыл бұрын

refreshingly straightforward yet very educational youtube video

@BrentColeman Жыл бұрын

Glad you found it useful!

@patriciabomar8059 11 ай бұрын

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 11 ай бұрын

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 10 ай бұрын

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

@chellelaclare6748 9 ай бұрын

@@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 9 ай бұрын

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 9 ай бұрын

@@BrentColeman You da best! 🙂

@nadiaperalta301 Ай бұрын

so helpful! thank you! That felt empowering.

@BrentColeman Ай бұрын

Glad it was helpful!

@0225ding 2 ай бұрын

Super helpful! Thank you so much for sharing the method

@BrentColeman 2 ай бұрын

Glad it was helpful!

@quigleyscorner4881 4 ай бұрын

This took away my headaches, excellent.

@BrentColeman 4 ай бұрын

Glad it was helpful!

@Premiumcribs Жыл бұрын

thank you so much for this well detailed video

@BrentColeman Жыл бұрын

Appreciate the comment - glad you enjoyed it!

@EmpeyRealtySanDiego 7 ай бұрын

This is amazing, thank you!!

@BrentColeman 7 ай бұрын

Glad it was useful!

@gpop0 12 күн бұрын

Very helpful, Thanks

@BrentColeman 12 күн бұрын

Glad it was helpful!

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

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

@gregorymalachilittle681 8 ай бұрын

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!

@BrentColeman 5 ай бұрын

Heya! Sorry for the late reply. The formula has a component that looks at the 'instalment' number. So it automatically determines the variable nature of interest costs as the loan amortises. If I was doing a specific interest calculation between dates (e.g. rate/365 * running balance * days between periods), then I'd have selected B9 and dragged the formula down. Hope this helps!

@jteichma 6 ай бұрын

Great video! Appreciate it.

@BrentColeman 6 ай бұрын

Glad it was helpful!

@andrewerickson5775 3 күн бұрын

Super helpful! Solid content. Perhaps slow down a tad bit. It was difficult to constantly have to pause because you talk really fast. Overall, thank you!

@BrentColeman 3 күн бұрын

Thank you! Will keep that in mind for future videos :)

@TruuMoo 4 күн бұрын

This video is super helpful, but still not entirely what I'm hoping to achieve with my spreadsheet. Let's say I wanted a "remaining payment" cell and wanted each month's interest accruement to be added in real time (every month for example). How would I go about doing that?

@BrentColeman 4 күн бұрын

Sorry I'm not sure I follow. Would this be a cell which captures all instalments/interest with a due date after [current date]?

@TruuMoo 4 күн бұрын

@ yeah, I suppose so. what I have currently is the estimated total (what I’ll have paid in 10 years including interest) minus what I’ve paid. Which is still useful, but if I pay _over_ my monthly installment then that estimated total is going to change, making the rest of my numbers inaccurate

@chellelaclare6748 9 ай бұрын

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!)