Excel Car Loan Amortization Template: Build Your Own

How-to basics · Updated November 22, 2026

You can create a car loan amortization schedule in Excel using the PMT, IPMT, and PPMT functions, or download a pre-built template from Microsoft’s template gallery. This article shows you how to build one from scratch, explains the math behind it, and helps you avoid common errors.

Why Use an Amortization Schedule for a Car Loan?

An amortization schedule breaks down every monthly payment into the portion that goes toward interest and the portion that reduces the principal balance. For a car loan, this is useful because it shows you exactly how much interest you pay over the life of the loan, how long it takes to build equity, and what happens if you make extra payments. Unlike a mortgage, car loans are typically shorter (3–7 years) and have fixed interest rates, so the schedule is straightforward to calculate.

How Car Loan Amortization Works

Each month, your payment stays the same (assuming a fixed rate). Early in the loan, most of the payment goes to interest because the outstanding balance is high. As the balance decreases, the interest portion shrinks and more of your payment goes to principal. The formula for the monthly payment is:

=PMT(rate, nper, pv)

Where rate is the monthly interest rate (annual rate divided by 12), nper is the total number of payments (loan term in months), and pv is the loan amount (negative because it’s money you owe). For example, a $25,000 loan at 6% annual interest for 60 months gives a monthly payment of:

=PMT(6%/12, 60, -25000)

This returns $483.32. The schedule then uses IPMT and PPMT to split each payment. For month 1, interest is =IPMT(6%/12, 1, 60, -25000) which equals $125.00, and principal is =PPMT(6%/12, 1, 60, -25000) which equals $358.32. The balance after month 1 is $24,641.68.

Step-by-Step: Build Your Own Template

Open a new Excel workbook. In row 1, enter labels: Loan Amount, Annual Interest Rate, Loan Term (months), Start Date, and Monthly Payment. Put the values in column B (e.g., B1=25000, B2=6%, B3=60, B4=2024-01-01). In B5, enter the payment formula:

=PMT(B2/12, B3, -B1)

Now create the schedule table. In A8 put “Month”, B8 “Payment”, C8 “Interest”, D8 “Principal”, E8 “Balance”. In A9, enter 1. In B9, reference the payment: =$B$5. In C9, enter the interest for month 1: =IPMT($B$2/12, A9, $B$3, -$B$1). In D9, enter principal: =PPMT($B$2/12, A9, $B$3, -$B$1). In E9, enter the starting balance minus principal: =$B$1-D9.

For month 2, in A10 enter 2, B10 reference the same payment, C10 use =IPMT($B$2/12, A10, $B$3, -$B$1), D10 use =PPMT($B$2/12, A10, $B$3, -$B$1), and E10 use =E9-D10. Then drag the formulas down to row 68 (for 60 months). The final balance should be $0.00 (or very close, due to rounding).

To make it more readable, format the currency cells and add a total row using =SUM(C9:C68) for total interest paid.

Using a Pre-Built Template vs. Building from Scratch

Microsoft offers a free “Loan Amortization Schedule” template in Excel’s template gallery (File > New, search “loan amortization”). This template includes extra features like extra payment options and a chart. However, building your own gives you full control and helps you understand the math. If you choose a template, check that it uses the correct formulas and that the payment matches your lender’s quote. A common issue with templates is that they may not handle extra payments correctly or may use annual interest without converting to monthly.

Common Mistakes and How to Avoid Them

  • Using annual rate instead of monthly rate: Always divide the annual rate by 12. In Excel, write 6%/12 or 0.06/12, not 6%.
  • Incorrect sign for loan amount: The pv argument in PMT must be negative to get a positive payment. If you get a negative payment, flip the sign.
  • Forgetting to set the number of periods correctly: If your loan term is 5 years, use 60 months, not 5.
  • Rounding errors: Excel calculates to 15 digits, but if you round the payment to cents, the final balance may not be zero. Use the full precision in formulas, or round only for display.
  • Extra payments not applied: If you make extra payments, you need to adjust the schedule manually. One way is to reduce the balance directly and recalculate subsequent interest.

Comparing Loan Scenarios with Your Template

You can use your template to compare different loan terms or interest rates. For example, change the term from 60 months to 48 months and see how the payment increases but total interest decreases. To do this, simply change B3 and watch the payment and total interest update. You can also add a column for “Extra Payment” and adjust the balance accordingly. For a more advanced comparison, use Excel’s DATA TABLE feature (Data > What-If Analysis > Data Table) to see payments at multiple rates and terms simultaneously.

Frequently Asked Questions

How do I calculate the total interest paid on a car loan in Excel?

Sum the interest column in your amortization schedule. For example, if your interest is in C9:C68, use =SUM(C9:C68). Alternatively, you can use the CUMIPMT function: =CUMIPMT(rate, nper, pv, 1, nper, 0) returns the cumulative interest for the entire loan.

Can I use this template for a used car loan with a different interest rate?

Yes, just change the loan amount and interest rate in cells B1 and B2. The schedule recalculates automatically. The same template works for any fixed-rate installment loan, including personal loans or RV loans.

What if my loan has a balloon payment or variable rate?

This template assumes a fixed rate and equal payments. For a balloon loan, you’d need to adjust the final payment to include the balloon amount. For a variable rate, you’d need to change the rate each period, which is more complex and may require a different structure.

How do I add extra payments to the template?

Add a column for “Extra Payment” and subtract it from the balance in addition to the regular principal. For example, if your extra payment is in F9, then the balance formula becomes =E9-D9-F9. Ensure the extra payment doesn’t exceed the remaining balance.

Why does my final balance not equal zero?

This is usually due to rounding. The PMT function returns a precise value, but if you round the payment to two decimals, the last payment may be slightly off. To fix, force the last payment to equal the remaining balance plus interest, or use the ROUND function on the payment but not on the balance calculations.

Is it better to get a shorter loan term to save on interest?

Generally, yes, because you pay interest for fewer months. For example, a $25,000 loan at 6% for 48 months has a payment of $586.04 and total interest of $3,129.92, while the same loan for 60 months has a payment of $483.32 and total interest of $3,999.20. You save $869.28 in interest by choosing 48 months, but your monthly payment is about $103 higher. Always check your budget.

Remember that any spreadsheet is a planning tool, not a substitute for your lender’s official amortization statement. Confirm the exact payment and interest with your lender, and consult a financial advisor for advice on your specific situation.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.