Disclosure regarding our editorial content standards.
Car loans are never going to cost you only the list price of the vehicle you’re purchasing divided by the amount of months you have to pay it off. Instead, the actual amount you’re paying depends on one thing: interest—which is basically the bank’s profit for the loan collected over time.
It’s important to remember that banks don’t offer loans as a public service. If it weren’t for the interest charged by lenders, they wouldn’t have an incentive to offer loans, and you might not be able to buy things, like your dream car, as a result. With that said, though, it’s important to be smart about how much you’re paying the bank, and here’s one way to do just that.
First, remember that the higher the interest you pay, the more you’ll end up paying over the lifetime of the loan. However, it doesn’t take a financial wizard to calculate what your loan payment will actually be. If you’re shopping for a car online and want to quickly run the numbers on your monthly payment, it’s a good idea to go ahead and do so.
Estimating your monthly car payments
Luckily, you can very easily calculate your monthly payment, including interest, in Excel. There are many online payment calculators available as well—you may want to double-check their work using these steps.
This formula does not include things such as tag and title fees, destination charges, etc. It is a tool to give you a basic estimate of the cost of your loan.
Follow these steps to calculate your monthly car payment in Excel:
1. Open a new Excel worksheet.
2. Enter the variables for your specific loan:
- Balance—the price of the car, minus any down payment or trade-in value of your current vehicle.
- Interest rate (the interest rate divided by the number of accrual periods per year—for instance, a 6% interest rate divided by 12 months – .06/12 = .005)
- Periods (how many months you will be paying the loan—for instance, a 5 year loan has 60 pay periods – 5*12 = 60)
- Add a section for Monthly Payment—this will be calculated soon!
3. Insert the correction function in the cell next to Monthly Payment.
- Click the cell next to Monthly Payment. In our example, this is cell B4.
- Insert a formula by clicking the Formulas tab at the top of Excel, then clicking Insert Function.
- Find the PMT Excel formula and insert it by clicking “OK.”
4. Plug in the information you entered in Step 2.
- Rate = Interest rate (B2)
- Nper = Periods (B3)
- Pv = balance (B1)
- You don’t need to enter anything for “Fv” or “Type.”
5. Hit “OK.”
- Presto—the monthly payment will be displayed in the correct field*. In this example, the monthly payment is $193.33.
*We’ve said this already, but it bears repeating—this is a handy way to get an estimate on a monthly payment for a car loan. Your actual experience may vary—for instance, your existing car may not get as high of a trade-in value as you expected, or your car loan will include fees you did not anticipate while shopping online, such as service warranties, title and tag fees, etc. This guide is to help you get an estimate.
If your credit has been negatively impacted by a loan or other credit account, contact our team today to learn more about your options.
