The uninformed car buyer typically haggles with a dealer over a monthly payment without understanding where that number came from. You can tip the negotiating scales in your favor by doing some homework on Microsoft Excel before you shop. Use Excel to calculate your possible loan amount based on your down payment and trade-in and to calculate your monthly payment based on various loan terms. Before a car dealer writes a number on a piece of paper and slides it across the table, you’ll have your own idea of a price and payment.
Step 1
Click Cell A1. Type the car’s price before taxes and registration, the sales tax rate as a decimal and the registration charges in Cells A1 through A3, respectively. Press “Enter” after typing in each cell. For example, assume a car’s price is $30,000 before taxes and registration, the tax rate is 7 percent and registration is $500. Type “$30,000” in Cell A1, “0.07” in A2 and “$500” in A3.
Step 2
Click Cell A4. Type the amount of your down payment in cell A4 and the value of your trade-in in Cell A5. Press “Enter” after typing each amount. In this example, assume you will trade in a car worth $5,000 and pay $5,000 down. Type “$5,000” in Cell A4 and “$5,000” in Cell A5.
Step 3
Click Cell A6. Type “=A1*(1+A2)+A3-A4-A5” and press “Enter.” Excel adds your sales tax and registration to the price, then subtracts your down payment and trade-in value to calculate your loan amount in Cell A6. In this example, Excel shows a $22,600 loan amount in Cell A6.
Step 4
Click Cell A7. Type the loan’s annual interest rate as a decimal in Cell A7 and the length of the loan in years in Cell A8. Press “Enter” after typing in each cell. In this example, assume your annual interest rate is 4 percent and the loan is for 4 years. Type “0.04” in Cell A7 and “4” in Cell A8.
Step 5
Click Cell A9. Type “=PMT(A7/12,A8*12,-A6)” and press “Enter.” Excel uses the values of your loan amount, interest rate and loan length in Cells A6 through A8 to calculate your monthly payment in Cell A9. The formula converts the annual interest rate to a monthly rate and converts the loan length from years to months to match the rate to the payment period. Concluding the example, type the formula to get a $510.29 monthly payment in Cell A9.
References
Tips
- Input different car price, down payment and trade-in amounts in Cells A1 through A5 and different loan terms in Cells A7 and A8 to analyze different loan scenarios. Excel automatically recalculates your new payment in Cell A9.