How to Calculate a Car Loan in Excel

By Carter McBride

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.

Step 1

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

Step 2

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 3

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 4

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

Step 5

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