How to Create an Auto Loan Calculator

The easiest way to make an auto loan calculator is to use the PMT function embedded in Microsoft Office Excel 2003 (or later). The PMT function is a simple way to calculate the payment for any loan based on constant (same-amount) payments with a constant (non-changing) interest rate. The MS Excel syntax for using the PMT function is: PMT(rate,nper,pv). The payment amount calculated by MS Excel returns payment amounts for principal plus interest. It does not include other items such as taxes, reverse payments or penalties.

Instructions

How to Start the Auto Loan Calculator

1

Click on cell A1 and type Purchase Price.

2

Click on cell A2 and type Down Payment.

3

Click on cell A3 and type Amount of Loan.

4

Click on cell A4 and type Interest Rate.

5

Click on cell A5 and type Number of Monthly Payments.

6

Click on cell A6 and type Monthly Payment.

7

Click on cell B3 (currency amount of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =B1-B2.

8

Click on cell B6 (monthly payments of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =PMT(B4/12,B5,B3).

9

Left-click on the A column to highlight it, then right-click and choose column width, enter the number 25 and click return. This will increase the width of the column so all the characters can be seen.

10

Left-click on the B column to highlight it, then right-click and choose format cells, click on the Number tab, locate the category Currency and click on it. Verify that the decimal places are set to 2 and the symbol is set to $. Click OK to return to the worksheet.

11

Left-click on the B column to highlight it, then right-click and choose format cells, click on the Number tab, locate the category Currency and click on it. Verify that the decimal places are set to 2 and the symbol is set to $. Click OK to return to the worksheet.

12

Left-click on the B5 cell to highlight it, then right-click and choose format cells, click on the Number tab, locate the category Number and click on it. Verify that the decimal places are set to 0. Click OK to return to the worksheet.

13

Left-click and drag on cells A1 through A6 to select those cells, then right-click and choose Format Cells, click on the Patterns tab, locate the color box for yellow and click on it. Click OK to return to the worksheet.

14

Left-click and drag on cells B1 through B6 to select those cells, then right-click and choose Format Cells, click on the Patterns tab, locate the color box for green and click on it. Click OK to return to the worksheet.

15

Left-click and drag on cells A1 through B6 to select those cells, then right-click and choose Format Cells, click on the Border tab, locate the preset with the Inside and click on it. Locate the preset with the Outline and click on it. Click OK to return to the worksheet.

16

Save the worksheet for later use.

How to Use the Auto Loan Calculator

17

Open the worksheet.

18

Click on cell B1 and enter the amount of the purchase price of the car and click return.

19

Click on cell B2 and enter the down payment amount of the car and click return.

20

Click on cell B4 and enter the yearly interest rate of the car loan and click return.

21

Click on cell B5 and enter the number of monthly payments that will be made on the car and click return. The monthly payment will be calculated in cell B6.