The dataset below details the Cost of Car, the Down Payment, the Interest rate and the period to pay the loan.
Step 1 – Compute the Total Payable Interest to Calculate the Car Payment in Excel
 Enter the following formula in C10 to calculate the Financed Amount.
=C4C5
C4 represents the Cost of Car and C5 refers to the Down Payment.
 Press ENTER.
This is the output.
 Enter the following formula in C11.
=PMT(C6/12, C7*12,C10)
C6 represents the Interest Rate, C7 indicates the Years, and C10 represents the Financed Amount .
Formula Breakdown
 =PMT(C6/12, C7*12,C10) → It returns the monthly payment.
 C6 → rate argument.
 C7 → nper argument
 C10 → pv argument


 Output → $475

 Press ENTER.
Note: A negative sign is used before the pv argument, as payments generate a negative cash flow.
This is the output.
 Enter the following formula in C12.
=C11*C7*12C10
 Press ENTER.
You will see the Total Interest:
Read More: How to Calculate Monthly Payment with APR in Excel
Step 2 – Calculate the Starting Balance
 Enter 0 and 1 in the Month column.
 Drag down the Fill handle.
 Drag the Fill Handle until you reach 60 (60 months).
This is the output.
 Enter the following formula in F15.
=C10
 Press ENTER.
This is the output.
 Enter the following formula in C16.
=F15
 Press ENTER.
You will see the Starting Balance in Month 1.
Read More: How to Calculate Loan Payment in Excel
Step 3 – Determine the Monthly Interest to Calculate the Car Payment in Excel
 Use the following formula in D16.
=C16*$C$6/12
C16 refers to the Starting Balance in Month 1, and $C$6 represents the Interest Rate.
 Press ENTER.
Note: An absolute cell reference is used in C6 ($C$6). As the loan is paid monthly, the Interest Rate is divided by 12.
You will see the Monthly Interest:
Read More: How to Calculate Auto Loan Payment in Excel
Step 4 – Calculate the Monthly Payment
 Use the following formula in E16.
=IF((C16+D16)>$C$11,$C$11, C16+D16)
$C$11 refers to the Monthly Payment, and D16 represents the Interest.
 Press ENTER.
You will see the Payment amount:
Read More: How to Calculate Monthly Mortgage Payment in Excel
Step 5 – Calculate the Ending Balance
 Use the following formula in F16.
=C16+D16E16
E16 represents the monthly Payment.
 Press ENTER.
This is the output.
Step 6 – Create a Monthly Payment Chart to Calculate the Car Payment in Excel
 Select C16:F16.
 Drag down the Fill Handle to see the result in the rest of the cells.
Practice Section
Practice here.
Download Practice Workbook
Related Articles
 How to Calculate Coupon Payment in Excel
 How to Calculate a Lease Payment in Excel
 How to Calculate Balloon Payment in Excel
 How to Calculate Down Payment in Excel Using VLOOKUP
<< Go Back to Calculate Payment in Excel  Excel for Finance  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!