How to Calculate a Car Loan in Excel

Calculating loan payments requires a long algebraic formula. However, by using Microsoft Excel, a user can drastically reduce the calculation times. In addition, using Microsoft Excel reduces the risk of making a mathematical error. To calculate payments in Microsoft Excel, use the PMT function.

...
Calculate car loan payments on Microsoft Excel

Step

Label cell A1 as Interest Rate. Label cell B1 as Months of Repayments. Label cell C1 as Loan Amount.

Step

Type "=Interest Rate/12" in cell A2. Replace Interest Rate with the yearly interest rate. For example, if a car loan has 6 percent interest, type "=0.06/12"

Step

Type "=Years of Repayment_12" in cell B2 to calculate the number of months for repayments. Replace Years of Repayment with the years of the loan. For example, a three year car loan would be "=3_12"

Step

Type the loan amount in cell C2. For example, $20,000 for a $20,000 loan.

Step

Type "=PMT(A2,B2,-C2)" in cell D2. This calculates the monthly payments on the car loan.