Use Excel to Plan Your Next Car Loan

October 26, 2004

Excel is great at doing calculations for loans. Most people know how to use basic Excel functions such as =SUM() or =AVERAGE(), but there are many more powerful functions available in Excel. One such function will help you to plan your next car loan.

Open a blank spreadsheet and enter three lines with Price, Term and APR Interest rate.

The function to calculate a loan payment is =PMT(). I can never remember the order of the arguments for this function. That is OK - the Excel Function Wizard can help you to enter the function successfully. In cell B4, start to enter the function: =PMT(

In Excel 2002 or later, the tooltip will appear showing all of the required arguments. Sometimes, though, the arguments can appear rather cryptic. What is PV and nper? After typing the opening paretheses for the function, hit Ctrl + a. The Function Wizard will appear.

The Function Wizard shows each parameter. Parameters shown in bold such as Rate, Nper, Pv are required. Parameters shown not in bold are optional. When you click into each field, the bottom of the function wizard tells you more about the parameter. In the image above, you can see that Rate is the interest rate for the period. Their example says to use 6%/4 for quarterly payments. Since we have monthly payments, we want to use B3/12. With the mouse, touch cell B3. Type /12.

Tab down to the NPer argument. The bottom of the function wizard explains that this is the number of payments.

Touch cell B2. Tab to the PV field to see that PV is the present value.

This is the non-intuitive part. Since you are borrowing money from the bank, you will be $29,000 in debt. Type an negative sign and touch cell B1. You can now see that Excel offers a provisional solution of $559 in the bottom of the wizard.

Click OK to enter the function.

Creating Scenarios

Now that you have the range of B1:B4 working, copy those cells and paste several times in columns C through F. Change the prices, terms, or interest rates to see the effect on the payment.

Using Goal Seek

What if you know that you want your payment to be $525? You can use the Goal Seek function. Put the cell pointer in cell G4. From the menu, select Tools - Goal Seek. In the dialog box, say that you want to set cell G4 to $525 by changing the price of the car in G1.

If Goal Seek can find a solution, you have the option to accept it or not.

Summary

Use Excel before visiting the car dealership the next time you need to buy a car. To read more about using dates in Excel, check out Guerilla Data Analysis E-Book