Auto Loan Amortization Table Excel Calculator

An auto loan amortization table shows each monthly payment split between interest and principal, plus the remaining balance. This calculator estimates the payment and total interest from your loan amount, APR, and term; you can use the same figures to build a month-by-month table in Excel.

Formula

The calculator uses the standard fixed-payment loan formula. It converts the annual percentage rate to a monthly rate and the term in years to a number of monthly payments:

• Monthly rate, r = APR ÷ 1,200, where APR is entered as a percentage per year.
• Number of payments, n = loan term in years × 12.
• Monthly payment, M = P × r ÷ [1 − (1 + r)⁻ⁿ].
• Total interest = M × n − P.

P is the amount financed in dollars, r is the monthly interest rate as a decimal, n is the number of monthly payments, and M is the monthly payment in dollars. The displayed payment is rounded to cents. The total-interest result uses that displayed rounded payment multiplied by the number of payments, then subtracts the original loan amount.

Worked Example

Using the calculator’s defaults: P = $20,000, APR = 8%, and a five-year term.

1. Convert the APR to a monthly rate: r = 8 ÷ 1,200 = 0.0066667.
2. Convert the term to payments: n = 5 × 12 = 60.
3. Calculate the payment: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53 when rounded to cents.
4. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

These figures represent a level-payment estimate under the calculator’s assumptions. The lender’s payment schedule may differ if its amount financed, payment dates, rate calculation, or rounding method differs.

Build the Amortization Table in Excel

Excel can calculate each period’s interest, principal paid, and ending balance. First, enter the loan amount, annual APR, and term in separate cells. For example, place $20,000 in B1, 8% in B2, and 5 in B3. If B2 is formatted as a percentage, Excel stores 8% as 0.08; use that value consistently.

Create columns for payment number, beginning balance, payment, interest, principal, and ending balance. In the first payment row, use formulas equivalent to:

• Beginning balance: the original loan amount.
• Payment: the fixed monthly payment, calculated with =-PMT(B2/12,B3*12,B1). The minus sign makes the result positive when the loan amount is entered as a positive value.
• Interest: beginning balance multiplied by APR divided by 12.
• Principal: payment minus interest.
• Ending balance: beginning balance minus principal.

For the next row, link its beginning balance to the prior row’s ending balance, then copy the formulas down for the remaining months. Format money cells to two decimal places. The PMT formula assumes a constant rate and equal payments; it does not automatically include a down payment, taxes, fees, add-ons, or a changing rate. If any of those are financed, include them in the amount financed only when that matches the actual contract.

How to Read the Schedule

At the start of a standard amortizing loan, the balance is largest, so the interest portion of the payment is also relatively large. As principal is paid down, interest for later periods generally falls and more of the fixed payment goes toward principal. The balance should approach zero after the final scheduled payment, subject to rounding.

A schedule is useful for seeing timing and balance, not just the monthly amount. It can show how much principal remains after a chosen number of payments and how total interest accumulates over the term. The calculator above summarizes payment and total interest; the month-by-month Excel rows provide the detailed breakdown.

Common Excel Errors and Limits

• Entering APR incorrectly: If a cell contains 8% as a percentage, divide by 12 for a monthly rate. If it contains the number 8, use 8/100/12 instead.
• Mixing years and months: The term in the calculator is years; Excel’s payment count must be years multiplied by 12.
• Rounding every intermediate value: Keep full precision in formulas and format cells to display cents. Rounding the balance or interest in every row can create a small leftover balance.
• Last-row mismatch: A real final payment may be adjusted by a few cents because of rounding or payment timing. Do not force a table’s final balance to zero by changing earlier payments without checking the contract schedule.
• Using sticker price as principal: The amount financed may differ from the vehicle price because of a down payment, trade-in, taxes, fees, or optional items. Use the contract’s amount financed for a closer estimate.

The formula models a fixed APR with monthly payments and a level-payment structure. It does not reproduce every lender’s day-count convention, payment-date treatment, late charges, skipped or irregular payments, fees, or contract-specific adjustments. An APR may reflect costs that are not represented as a separate monthly cash flow in this simplified table.

Assumptions

• The interest rate is fixed throughout the term.
• Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
• Interest is modeled monthly at APR divided by 12.
• Fees, taxes, insurance, optional products, and other charges are not separately included; use the actual amount financed if they are part of the loan balance.

Frequently Asked Questions

What Excel formula calculates an auto loan payment?
Use =-PMT(APR/12, years*12, amount_financed) when the APR cell contains a percentage and the amount financed is positive. This returns a positive monthly payment under the usual end-of-period payment assumption.

How do I calculate monthly interest in an amortization table?
Multiply the beginning balance for that month by the annual APR divided by 12. Subtract that interest from the payment to find the principal portion.

Why does my Excel balance not reach exactly zero?
Displayed cents are rounded, while formulas may retain extra decimal places; alternatively, the final payment or contract assumptions may differ. Keep precision in calculations and compare the schedule with the lender’s figures.

Does an auto loan amortization table include a down payment?
Not by itself. A down payment reduces the amount financed; enter the resulting financed balance rather than the vehicle’s full price. Taxes, fees, and trade-in amounts affect the estimate only if reflected in that balance.

Can I use the table for a variable-rate loan?
This formula assumes a fixed rate. A rate change requires recalculating future payments or applying the contract’s adjustment method, which this calculator does not model.

This result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• WIC Qualifying Income: Limits and Eligibility
• Total Qualifying Income Calculator and Guide
• SNAP Qualifying Income: How Eligibility Is Calculated

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2