Quick Navigation

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

**is going to be**

*nper***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

**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:**

*type*=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!

## Other posts in this series...

- Car Loan Calculator, Part 1: Collecting Variables and Calculating Payments Using the PMT Function
- Car Loan Calculator, Part 2: Building a Payment Table and Calculating Interest Using the SUMIF Function
- Car Loan Calculator, Part 3: Scenario Analysis Using a Data Table
- Car Loan Calculator, Part 4: Data Visualization Using Conditional Formatting, Charts, and VBA

“=SUM(C3,C4,C9*C7) ” should read =SUM(C3,C4,C10*C7)

C10 instead of C9

This depends on where you put you Pmt formula. I put mine in C9.

How would you change the formula for the payment to account for 45 days to first payment?

WHAT IF THE FIRST PAYMENT DATE IS SET OUT 45 DAYS?

Leave a .5 on for term (72 months = 72.5) or put fx=(Nper)+.5

That doesn’t work. That makes the payment lower and it should make it higher going 45 days to first payment.

To me, increasing the Nper .5 would tell the formula than an extra half payment is being made at the end, thus reducing all the other payments. Which would not be correctly representing the payment.

I think I found the solution..still have to test it. If you figure the “per diem” or the daily interest on the loan by taking the (rate X amt financed)/365, that should tell you the interest earned daily on that amt. Then, in your PMT formula, you should add into your PV the extra amount of interest you earned over 30 days.

For example the pmt formula should look like this:

PMT(RATECELL/12,NPERCELL,AMTFINCELL+(PERDIEMCELL*(DAYSTOFIRSTPMTCELL-30))) where RATECELL=the cell that has the APR, NPERCELL=the term of the loan in months, AMTFINCELL=Total Loan Amount cell, PERDIEMCELL=the cell calculating your per day interest rate, DAYSTOFIRSTPMTCELL=The cell containing the # of days to your first pmt.

Thank you! Super helpful. All best, E