How To Make a Loan Amortization Table for a Car Loan

[@TDensmores]

I appreciate this, but it assumes the audience already knows the shortcuts, it could use a little slower paced more thorough explanation.

[@CalonHeindel]

Great feedback I appreciate this!

[@jjverlingieri]

Thanks. My car loan doesn't look so bad afterall...lol...have to try and make principal payments. Thanks again.

[@CalonHeindel]

Glad this was helpful for you!

[@jameslondon858]

Great video!!! In the beginning of the video, I’m still a little bit confused on what the formula was to come up with the $539.06 payment…..

[@aerynsunn7500]

Thank you sooooo much. This is what I’ve been looking for!

[@CalonHeindel]

Glad this was helpful! Love to hear it!

[@reginaldbracken6312]

This video has been a great help!

[@CalonHeindel]

Glad this was helpful!

[@chrisa.9977]

Thanks. For some reason the calculation is dollars of on my actual loan from the finance company.

[@nghongtong7124]

hi, how do you lock the cell when you drag down the payment tab?

[@CalonHeindel]

Lock a cell you can use “ctrl+f4” on most computers. If that doesn’t work use can manually use dollar signs like this “$A$4” for example

[@czg7331]

What if you're on a mac using Numbers? I got all the way to locking the cell! Thanks@@CalonHeindel

[@pickleman5014]

Hey Colon, thanks for the video man!

[@CalonHeindel]

Appreciate the support!

[@hunterturnbull]

Thank you for the help! Is there a way to calculate for an annually compounded loan?

[@CalonHeindel]

Yes there definitely is. I don’t have a video on that yet unfortunately. That’s a great video idea for the future.

[@keamogetswetsikang8709]

Does the principal amount include vat? Or we remove it?

[@CalonHeindel]

Generally loans In the US don’t have VAT

[@therealsteve3354]

good day sir. just trying my luck really, really hope you or someone replies to this comment. how do do you add the lock so that you can project the correct number every time?

[@CalonHeindel]

You can hit FN + F4 on most keyboards. Or add dollar signs manually

[@therealsteve3354]

@@CalonHeindel thank you very much. i already went through some of your Excel shortcuts videos and found my answer, i am a student in Cape Town South Africa. Exams are coming up shortley and i am finding your videos very helpful. do you perhaps also have videos on Sinking funds

[@diegorendon3782]

This is a great video. Thank you.

[@CalonHeindel]

Thanks! Glad you enjoyed it!

[@yongkahchun4964]

hi why I am different from your answer. I using the car loan calculator which is insert the loan amount, interest, and tenure is different from yours

[@CalonHeindel]

Tenure?

[@mjanelagarde7776]

second-hand Toyota Corolla car is priced at P320,000. The owner is asking for a 30% down payment and an installment term for 3 years at a monthly interest rate of 0.5%. How much is the amortization ?

[@CalonHeindel]

Not sure what you mean by “P320000”. However if you follow the along and create the template you can creat your own amortization table and find the monthly payment amount

[@jesus-b5w8e]

6,270 pesos. Binili mo ba?

[@jesus-b5w8e]

dapat mag down payment ka ng 90,000 pesos at magbayad ng 6,270 pesos every month for 3 years.

[@ndihokubwayobertin6882]

thank you my friend you are good at accounting courses

[@CalonHeindel]

Thanks for the support. I appreciate that!

[@awithglasses6350]

Is it alright If the amortization table has negative numbers like after the 0?

[@CalonHeindel]

Once you’re table has negative payments the loan has been paid off by then. That just means you have more room on your table then the length of the loan

[@curri9111]

My question is where do I find or how do I create one that is accurate? Without it adjusting for the differences in the loan start date and 1st payment due date it won't be accurate. For example: You purchase an auto on 01/01/2022 but your first installment is not due until 2/14/2022 (45 days). This number of days is common in an auto loan. Any help would be appreciated!!

[@CalonHeindel]

Great question. You’re right a lot of car loans don’t require the first payment until 45 days after signing. If that’s the case you’ll just pay more interest for the first payment. Then after that the interest and payment should be the same month to month

[@curri9111]

@@CalonHeindel I tried getting the perdiem and adding it to the balance then running the schedule. Is that the incorrect way and if so how do you do it?

[@CalonHeindel]

@@curri9111 If im understanding you correctly then yes that would work. You could use the per diem interest to calculate the amount for the first 45 days

[@curri9111]

@@CalonHeindel thank you for your quick response!

[@mrrubot4678]

Very cool, thank you!

[@beverlymanuel8435]

What if you doubled up a few months and skip a payment? Do you then make the payment amount $0? Would that void the formula in that column?

[@CalonHeindel]

If you skipped a payment for a month or period you would count that as $0. Which, depending on the loan would, could change the monthly payment amount or add some sort of fee.

[@beverlymanuel8435]

@@CalonHeindel I see that the interest rate still applies if a payment is skipped. Thanks!

[@tabhardin5707]

I can’t find a lock button on mine I tried to slow it down but I’m still lost 🤦🏼‍♀️

[@CalonHeindel]

The lock button just needs $ signs. For example cell “C9” just needs to be “$C$9”. The keyboard shortcut is FN + F4

[@roger2bro]

thank you :fistbump:

[@CalonHeindel]

Glad this could help!

[@carlchristopher2534]

How to handle a partial month payment at the start of the loan. Say the bank wants to have your payment date at the end of the month. Say the 28th, but it is the 15th of the month when you start the loan. Also, there bank wants to give you say a minimum of 28 days before your first payment. So, using the dates I layed out, there would be 43 days of interest, not 30 in that first payment. How can we account for this ???

[@CalonHeindel]

Alright so the easiest way to do this would skip the first month calculations and account for this in the starting Period 0 Loan balance. Then continue the formulas as the video shows, just know when it says period 1 it really would then mean period 2.

[@carlchristopher2534]

@@CalonHeindel I'm gonna try but I'm thinking there's some technicalities in there that are a little hard to handle

[@CalonHeindel]

@@carlchristopher2534 Yeah you'll need to run a separate formula to figure that out specifically. I would have to look into the exact way to do that. Let me know if you can't get it figured out.

[@babs4376]

how do u lock

[@CalonHeindel]

Ctrl + F4. or you can manually put dollar signs anchor cells

[@abdirazzakmohamed3450]

Thank you Calon

[@CalonHeindel]

No problem. Thanks for the support

[@laycismyth6584]

what if the loan is 77 months and not specified in years?

[@laycismyth6584]

mine is daily compounding interest by the daily unpaid principal balance each day of billing cycle. Can you do an example of that?

[@CalonHeindel]

Just do 77 divided by 12. That will get you the number of years without having to change any formulas.

[@juliemckenna3244]

How to adjust with a fixed payment amount?

[@CalonHeindel]

Well you can do the normal calculations as the video shows do find the normal monthly payment. Then anything left over from your fixed payment amount can be put into the extra payments column.

[@nicklaswade1957]

Thank you good sir👏

[@CalonHeindel]

happy to help!

[@seancassidy8114]

Thanks man

[@CalonHeindel]

Appreciate the support!

[@darlarock9408]

If I pay my payments early my interest rate fluxuates

[@julietonuorah4779]

You are repaying a loan of $[A] by making payments at the end of every six months for {B] years. interest is {C]% compounded {DJ. Solve for the missing value and complete the partial amortization table below. Do not use units, negative values, or comma separators (eg. use 10000 not $10,000). {A] $42,000 {B} 8 {c} 5.92% {D} annually Pls can you tell me the PMT

[@elielsantos3921]

enquanto isso a gente aqui no Brasil com 12% ~ 15% de interest rate.

[@noodlechrist4958]

This is a "how-to". Why is this such a stuggle on KZbin?:Your video doesn't actually start until 1/3 in.

[@CalonHeindel]

Sorry to hear you didn't find this video helpful. Setting up the loan amortization table does start around 1 min into the video.

[@noodlechrist4958]

@@CalonHeindel Your video was helpful. Thank you and it was appreciated. My point your audience, like any how-to audience, is looking for a answer and usually a very quick one as we sift through 1000s of videos that may (or may not) have the answer. I'd love one creator to start with "VLOOKUP as Fast a Possible", or "Learn Excel's IF Function in 60 Seconds".

[@CalonHeindel]

@@noodlechrist4958 Ya that makes sense. I appreciate the feedback, I will keep that in mind for future videos. Thank you!