Buying a new or used car, for most people, is not a purchase made by writing a check or handing over cash for the full amount. Before signing for a loan through the dealership, bank, credit union or another lending institution, be sure to calculate auto loan payments to determine the impact on your budget. This article will review how to calculate auto loan payments with Microsoft Excel as well as some factors affecting the amount financed.
Determine the Amount to Finance
1. Deduct the trade-in value, if applicable, from the price of the car you wish to buy to get the estimated purchase price.
2. Calculate the amount of state sales tax and add it to the estimated purchase price. For example, a state with a 7 percent sales tax will add $1,050 on top of a purchase price of $15,000 to make it $16,050.
- Some states don’t allow a deduction of sales tax on trade-ins; you must pay tax on the total cost.
4. Subtract the amount you wish to pay as a down payment to get the amount you want to finance.
Use Microsoft Excel to Calculate Auto Loan Payments
1. Determine the monthly payment with the =PMT function in MS Excel. This example uses a $15,090 loan for 48 months at 7 percent interest.
2. Open MS Excel and type these descriptions on the first 4 lines in column A: “Rate,” “Number of Payments,” “Present Value” and “Future Value.”
3. Enter the following numbers in column B next to the descriptions: 7.00%, 48, 15,090 and 0.
4. Input the formula “=PMT(B1/12,B2,B3,B4)” in a cell below the numbers.
- Type “=PMT(“ and click on the cell with 7.00% so “B1” appears after the left parentheses.
- Type “/12,” (including the comma) and click on the cell with 48 to see “B2” appear.
- Type a comma after “B2” and click on the cell with 15,090 to see “B3” appear.
- Type a comma after “B3” and click on the cell with 0 to see “B4” appear.
- Type a right parenthesis at the end to complete the formula.
6. Change any of the variables, such as the finance amount or number of months, to see how the monthly payment changes.
- Check on the type of interest being charged. In most cases the lender will use the APR, or Annual Percentage Rate. However, some lenders use the Nominal Interest Rate, also known as the Stated Rate. At 7% compounded monthly, the APR is 7% while the Nominal Interest Rate is a higher 7.22%.
- Compare interest rates at local banks, credit unions, car dealerships and on the Internet. A few 10ths of a point can save you hundreds or even thousands of dollars in interest. Indirect financing from a dealer is cheaper if you meet “qualified buyer” standards; however, the dealer also takes a mark-up on a loan.