You’ve been learning individual Excel functions and quick tips to improve your work, but now it’s time to put them together to make a functional tool. Calculating the payments for a car loan is a pretty basic financial exercise. Google “Car Loan Calculator” and you’ll find no fewer than 31 million results! But with a few basic formulas and an Excel worksheet, you can make a payment calculator that better and more powerful than the majority of those online! Think you’re ready to start putting your Excel skills to use? Let’s get started!

In many ways, Excel was designed with finance calculations in mind. It has functions that specialize in figuring out payment schedules, calculating interest due, etc. Before we hand over the hard lifting to our favorite spreadsheet program, however, we have to decide what we are going to tell it.

Collecting the Data

The first step of many new financial models is data collection. In this case, we are building a car loan calculator, so we have to think about what variables the car buyer is working with. She might have some cash on hand to use for a down payment. She also might have a car to trade-in (or sell). Finally, there are the details of the loan. How much money is she borrowing? What is the interest rate, usually represented as an annual percentage rate (APR)? Finally, how long will she be borrowing the money, also known as the term?

Our first step is building a neat table to collect all this information. It’s useful to provide a separate cell for each variable, in case we need to change the values later. When you’re done, it should look something like this:

Making the Calculation with the PMT() Function

The key question for most people when taking out a car loan is “What will my monthly payment be?” Fortunately, Excel has been around the block a few times with this kind of question, and there is a special function just for this calculation, called PMT. The basic syntax for PMT is as follows:

=PMT(rate, nper, pv, [fv], [type])

Let’s break down the inputs:

The rate input is the amount of interest collected per period. Important: This is NOT the APR! The APR is an annual rate, but (most) car loans are paid monthly. Because of this, you need to divide the APR by 12. If your interest rate variable is in the same place as mine, the input will be C6/12.

The nper input is the number of periods the loan will be paid back over. Most car loans are 3-5 years. My loan term is already specified in months, but if you decide to use years, don’t forget to multiply by 12 to get the number of payments! My input for nper is going to be C7.

The pv input stands for present value, which is finance code for how big the loan is. Finance types are kinda weird, so they Excel likes to assume that the loan amount is negative. Since we usually like to see the loan as a positive number, we’ll change the sign right in the function. My loan amount is in C5, so my input will be -C5.

The remaining inputs, fv and type are for more complicated calculations and we don’t need them for our project, so we’ll leave them alone for now. If you are building your model exactly like mine, the formula for the monthly payment will look like this:

=PMT(C6/12,C7,-C5)

The last thing we may want to include is a total that shows our car buyer how much money she will end up paying. Remember, she’s potentially paying cash as a down payment, trading in a vehicle that has sale value, and she’s paying monthly payments for the duration of the loan. Let’s add a quick SUM formula that adds up those three values (overview of the SUM function here, if you need). My sum formula looks like this:

=SUM(C3,C4,C9*C7)

When you’re done, you should have a nice, clean calculator that can take the basic inputs of a loan to calculate a monthly payment!

Car Loan Calculator Example

My worksheet from this tutorial is available below. You can play around with different values for the interest rate, loan amount, and term to get different results. You can also download the sample file by clicking the Excel icon in the bottom right.

[iframe src=”https://skydrive.live.com/embed?cid=56D8503869383D0E&resid=56D8503869383D0E%21122&authkey=AA-mT6HFCqnIJxE&em=2&wdAllowInteractivity=False&AllowTyping=True&wdDownloadButton=True” height=”346″ width=”360″ frameborder=”0″ scrolling=”no”]

This is a great first step towards building a functional car loan calculator, but there are a lot of features we can add that will make it more powerful and useful. Check back for the next part in the series to learn how to break down the interest and principal payments to understand where all the money goes each month!