Using Microsoft Excel to Estimate Your Car Payments

car payments

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 — basically the bank’s profit for making the loan collected over time.

It’s important to remember that banks don’t make loans as a public service. If it weren’t for the interest charged by lenders, they simply wouldn’t be able to make loans, and you might not be able to buy 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 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.

The steps for calculating your monthly payment in Excel

1. Open a new Excel worksheet

Untitled

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!

Untitled

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.

Untitled

  • Find the PMT Excel formula and insert it by clicking “OK.”

Untitled

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.”

Untitled

5. Hit “OK.”

  • Presto – the monthly payment will be displayed in the correct field*. For this example, the monthly payment is $193.33.

Untitled

*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 provide an estimate

If you’re credit has been negatively impacted, contact us today to get started.

Carry on the conversation on our social media platforms. Like and follow us on Facebook and leave us a tweet on Twitter.

Posted in Finance
Learn how it works

Questions about credit repair?

Chat with an expert: 1-800-255-0263

Facebook Twitter LinkedIn