Use the PMT Function to Calculate Car Loan Payments and Cost of Financing
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- Demo file: / 31817534
The PMT function in Excel can be be used to determine the monthly payment for any fixed-rate loan. By providing information like car loan amount, interest rate, and months, we can calculate the exact monthly payment and then determine the total cost paid for a car.
Four years later, I found your video and it has helped me so much. Thank you for explaining it in such a simple way for your students to understand. Thank you for all you do.
My girl needed a bank statement and stub last week and I was worried about her loosing her not getting her car loan 😴 but everything good now only because of deelscafe good work 👍.
@Bitrus Sabo same here
This function was rather intimidating to me at first, but he explained it so well and put me at ease.
Ralph, this was amazing and so easy to understand! Thank you!
Great instructions! This is exactly what I needed. Thanks for putting this out.
I like how you teach your viewers to know how to do this. it's so clear and easy to understand thank you❤️💪
You're so welcome! However, don't get car loans...Pay cash!
Very clear! This is awesome!!! Thank you! Much aloha! ❤️🙏🏽⭐️
Fantastic! You explained it so well. Thank you.
And where do you put the state tax?
Best video ever regarding this topic
Thank you so much. I was looking for this 😊👍
Great job on the explanation. Ty!
This really helped. Thank you.
I always like your tutorial and your voice.
Thank you, simply explained
awesome video and super easy to understand, I did just like you demonstrated then I copy column then added a few columns so that Im able to display several scenarios. thanks for posting!!
Glad it helped! I read an article this weekend that the average new car price is now $47,000 and the average monthly car payment is $700. That is insane to me. I really like the idea of making side-by-side calculators so you can see the impact of a different down payment or a different interest rate.
@@SixMinutesSmarter yeah I believe this too as lower supply has created higher demand for cars so some dealers are adding "Market Adjustment" fee of $3k and last month I see theyre rasing it to $5k so thats my first qst when i call and if answer is yes then I hang up and call nxt dealership lol.
Good Job, I solved my exam question quickly.
Great vid. Thank you 👌
WOW! Thank you!
Thank you so much...😊.
you are amazing
THANK YOU
Brother what happened to the local tax they always include on every single vehiclr they sell
Thank you
Question for you @Six Minutes. Smarter. : I noticed in the Total Cost of Finance field that you did "=Total Paid-Cost of Car." This would have the finance cost also include the down payment instead of being the interest only, correct? Shouldn't the Cost of Finance be only interest since that is the amount you are forced to pay over what you borrowed? Am I thinking incorrectly on this? Thanks for your help!
Hello, Can you make a video of total cost of ownership of a car for 5yrs? Thanks
How would we do this in reverse? For example I’m paying $300 a month, for 72months (6 years), with an interest rate of 5%. What’s the formula to find the total loan amount? (I keep seeing =PV, but I’m confused at how when I use PV, the outcome is a lesser number then if I multiply monthly payment (300) by Term (72 months). How is it more expensive at 0%? I’m so confused and need help Ralph.
What if there was no loan borrowed. Would I just use the price of the product?
Why are you including downpayment amount in the amount financed for the formula on line 8?
Hello! QQ, can you do this formula while adding the number of days until the first payment and how that would affect the outcome
I am having an issue with this format.
It breaks my amortization schedule unless I set the interest to 0% totally confused here
I also got two biweekly paystub for my loan to be approved.
Would love a video on leasing a car with the spreadsheet
I'll look into that, but leasing a car is even worse than getting a car loan. Best to avoid it.
Bro I need help in excel
Is the first installment at time=0 or 1 month after?
And the estate taxes?
Many times people finance this cost too..
Credible cyber came thru tbh
W VIDEO
Awesome video but if you’re putting a down payment, the total cost of the car decreases, but you wrote the formula where the total cost increases. You need to enter a negative value to determine true price of the car. The way you entered it was taking on another $4500, which isn’t accurate. The total value borrowed would be $-4500 less and thus decreasing monthly payment and less interest owed.
Total price of the car must include the down payment. A big down pmt reduces loan and finance cost, but not the price.
what about the state taxes
but what if youre paying bi-weekly?
It is still approximation because taxes and fees are not included in final payment …
taxes and fees at purchase are often included in the purchase price. You could make fields for the car price, one for taxes, one for fees. Then, total that and make a total price. Use the total price in the calculation. Total price - down payment = loan amount.
Is there always going to be down payment? My teacher gave me a problem but did not mentioned anything about finding the down payment
No, there doesn't have to be a down payment. The down payment is used to reduce the amount of the loan. The loan amount is really what you use in the PMT function.
To make the calculator more realistic, I like having down payment amount and calculate the loan by subtracting the down payment from the car price. If you want, enter 0 as the down payment and you'll see the loan is the same as the car price.
You could even enter a negative value in the down payment and that would indicate you're borrowing more than the price of the car. This might occur if you were rolling older car trade-in debit into the new loan.
big question i want this bmw m4 cs really bad its 50,000 grand when i loan it and three years later the car price is depreacted like 20,000 grade well do i still have to pay that 50,000 grade or the price of what its going to be?
buy a Honda and save up. Three years later, buy the M4 for 30 grand. If you buy two M4s, i’ll take one.
@@SixMinutesSmarter i found this 2018 bmw m4 for 37,000 but its the regular one i truly want the cs one but i guess i should just get the 37,000 one and buy the other one in the uture because the msrp is gonna be like a whole 30,000 more just for the cs
Geez, I just looked up the BMW M4 CS. It's like $70,000 - $100,000 US. I'll have to stick with my 5-year-old Ford a little bit longer. Even better, invest the $70K in a fund averaging 11% for the next 20 years and you'll end up with $564,000.
@@SixMinutesSmarter also roth ira if you invest 230,000 over 20 or 30 years and you'll have 2 million and spending it down have 4 million I want the car because I'm young and if I start making 12,000 a month and put 5 thousand a month would be fun
can i hire you to create a calculator format in Excel if i provide you the details?
Can you do one including taxes
Easiest to create a couple new rows under car price. One row for taxes and another row for price+taxes.
You may pay taxes all at start along with down payment. Or some may roll taxes into the loan, increasing the balance and loan payments.
Taxes will vary by state, and some states (like Oregon) won't have taxes.
There could be some other fees to include, too. Destination charge and doc processing fees.
What about tax
yep, the price of the car should include taxes and other fees. Those will vary by state (no sales tax in Oregon ). However, i bet most people financing a car roll those costs into the loan.
for more detail, create a few additional rows under car price. include tax, fees, other. then have a row for total price. The rest would be the same. total price - down payment is loan amount.
Hello. Lets say I want to include the deposit in the price of the car, meaning the company pays the deposit and not the customer. How would I calculate that in Excel. Deposit may change from 10% / 15% / 20% / 30% / 40%
I'd create a couple of extra rows (cells) beneath original price. So, we might have list price of the car as $30,000, then have a value for deposit rate. That would be the value of 10%, 15%, etc. Then the cell below would be Modified car price. That would be =(original list price) - (original list price * deposit rate).
Now, you'd base your loan calculations off the modified car price. But the cost of finance would be based on the original list price.
This is an interesting scenario where a company benefit can really make a car lease or car loan a very good deal.