Excel Auto Loan Amortization Template: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can build a complete auto loan amortization schedule in Excel using the PMT, IPMT, and PPMT functions, or you can download a pre-built template from Microsoft’s official template gallery. The core formula is =PMT(rate, nper, pv), which calculates your fixed monthly payment; from there, you track each period’s interest, principal, and remaining balance.

What an Amortization Schedule Actually Shows

An amortization schedule is a table that breaks down every loan payment into two parts: the interest charged for that period and the portion that reduces the principal balance. For an auto loan, the schedule runs month by month from your first payment to your last. Early payments are mostly interest; later payments are mostly principal. This happens because interest is calculated on the remaining balance, which shrinks over time.

A typical Excel template includes columns for:

  • Payment number (1 through total months)
  • Payment date (optional but useful for tracking)
  • Payment amount (constant if the rate is fixed)
  • Interest paid in that period
  • Principal paid in that period
  • Remaining balance after the payment

Some templates also add a cumulative interest column, a running total of payments, and a summary area showing total interest and total cost of the loan.

How to Build the Template from Scratch

You don’t need a downloaded file. With a few formulas, you can create a flexible schedule in minutes. Here’s the layout and formulas for a basic template.

Assumed layout: Put the loan amount in B1, annual interest rate in B2, loan term in months in B3, and first payment date in B4. In row 7, create headers: A7 = Period, B7 = Payment, C7 = Interest, D7 = Principal, E7 = Balance.

In A8, enter 1. In B8, enter the payment formula:

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

This returns the monthly payment. For a $30,000 loan at 6% annual interest over 60 months, the result is $579.98. The negative sign on B1 makes the result positive.

In C8, calculate the interest for the first month:

=B1*(B2/12)

For the example, that’s $150.00. In D8, subtract interest from payment:

=B8-C8

That gives $429.98 of principal. In E8, compute the new balance:

=B1-D8

That leaves $29,570.02.

For row 9 onward, reference the previous row’s balance. In A9, enter =A8+1. In C9, use:

=E8*(B2/12)

In B9, copy the same payment formula or simply reference =B8. In D9, use =B9-C9. In E9, use =E8-D9. Then drag these formulas down to row 67 (for 60 payments). The final balance should be $0.00 or a few cents off due to rounding. To fix rounding, you can set the last payment to equal the remaining balance plus interest.

Using the Built-in Excel Amortization Template

If you prefer not to build your own, Microsoft provides a free template. In Excel for Microsoft 365 on Windows, go to File > New, type “loan amortization” in the search box, and choose “Loan Amortization Schedule” or “Auto Loan Amortization.” The template includes a summary section, a payment schedule, and a chart. On Mac, the path is the same: File > New from Template and search. For Excel on the web, go to File > New and search the template gallery.

The built-in template uses named cells and a table, so you only change the yellow input cells (loan amount, rate, term, first payment date). It automatically recalculates the schedule and totals. One caveat: some templates assume a fixed rate and equal payments, which is standard for most auto loans. If your loan has a variable rate or balloon payment, you’ll need a custom schedule.

Key Formulas and Functions Explained

Three functions do the heavy lifting in any amortization template:

  • PMT(rate, nper, pv) – returns the constant payment per period. Rate is the periodic rate (annual rate divided by 12 for monthly payments), nper is total number of payments, pv is the present value (loan amount). Use a negative pv to get a positive payment.
  • IPMT(rate, per, nper, pv) – returns the interest portion for a specific period. For example, =IPMT(B2/12, 1, B3, -B1) gives the interest for the first month.
  • PPMT(rate, per, nper, pv) – returns the principal portion for a specific period. =PPMT(B2/12, 1, B3, -B1) gives the first month’s principal.

You can use these to verify your manual calculations. For the $30,000 loan at 6% over 60 months, IPMT for period 1 returns $150.00, and PPMT returns $429.98. For period 12, interest drops to $123.41 and principal rises to $456.57.

Common Problems and How to Fix Them

Several issues come up frequently when people build or use these templates:

  • Payment shows as negative. This happens when you enter the loan amount as positive in the PMT function. Use -B1 or wrap the formula with ABS().
  • Final balance doesn’t reach zero. Due to rounding, the last payment may be a few cents off. Adjust the last payment to equal the remaining balance plus the last month’s interest. In the schedule, override the last period’s principal with the remaining balance.
  • Interest calculation uses annual rate instead of monthly. Always divide the annual rate by 12 in the formulas. If your rate is in B2 as 6%, use B2/12, not B2.
  • Dates don’t align. If you include payment dates, use the EDATE function to add one month to the previous date. For example, if the first payment date is in B4, then in the next row use =EDATE(B4,1).
  • Extra payments aren’t reflected. The standard template assumes equal payments. To add extra payments, insert a column for additional principal and subtract it from the balance. The formulas need to be adjusted manually.

Comparing a Pre-Built Template vs. Building Your Own

Both approaches have trade-offs. A pre-built template is faster and often includes charts and summary tables. It’s also less error-prone because the formulas are already tested. However, you might need to modify it for unusual loan terms, such as a balloon payment, a first payment due date that isn’t one month after the loan start, or biweekly payments. Building your own gives you full control—you can add extra payment columns, change the compounding frequency, or include a payoff date calculator. The trade-off is time and the risk of formula errors.

For most people, the built-in template is sufficient. If you need a custom schedule, building your own with the formulas above is straightforward and helps you understand exactly how your loan works.

Safety and Accuracy Notes for Loan Math

Always double-check your inputs. A single wrong interest rate or term can throw off every payment. When using the template, verify the first month’s interest manually: multiply the loan amount by the monthly rate. For a $30,000 loan at 6% annual, that’s $30,000 * 0.005 = $150. If the template shows something different, check the rate format—Excel stores percentages as decimals, so 6% is 0.06.

Also, be aware that the PMT function assumes payments are made at the end of each period. If your lender requires payments at the beginning (rare for auto loans), you’d use =PMT(rate, nper, pv, 0, 1). The fifth argument, type, set to 1 means payment at the beginning.

Finally, remember that this template is a planning tool, not a legal or financial document. Lenders may calculate interest using a different day-count method or round differently. Always confirm your actual payment amount and total interest with your lender before signing any agreement. If you’re considering early payoff, check whether your loan has prepayment penalties.

Frequently Asked Questions

Can I include extra payments in the template?

Yes, but you need to modify the schedule. Add a column for “Extra Payment” and subtract it from the balance after the regular principal payment. For example, if you pay an extra $100 in month 12, the new balance becomes =E11 - D12 - F12 where F12 is the extra payment. The interest for the next month will be lower because the balance is lower. You can also use a one-time lump sum; just enter it in the appropriate period.

What if my loan has a variable interest rate?

The standard PMT-based template assumes a fixed rate. For a variable rate, you’d need to adjust the interest rate for each period. One approach is to have a column for the annual rate that changes over time, and then use that column in the interest formula. For example, if the rate changes after 24 months, you’d update the rate in the cells for periods 25 onward. The payment may also change, so you’d recalculate the payment using the new rate and remaining term.

Why does the total interest seem so high?

Because interest is calculated on the declining balance, the total interest is the sum of all monthly interest charges. For a $30,000 loan at 6% for 60 months, the total interest is about $4,799. That’s normal for a 5-year loan. You can reduce total interest by making extra payments or choosing a shorter term, but that increases the monthly payment.

How do I calculate the payoff amount at a specific month?

The payoff amount is the remaining balance at that point plus any accrued interest. In your schedule, the remaining balance after a payment is the principal still owed. If you want to pay off the loan between payments, you’d need to calculate the interest for the partial period. Most lenders will give you a payoff quote. In Excel, you can use the FV function to find the future value of a loan after a certain number of payments: =FV(rate, nper, payment, pv). For example, after 12 payments on the $30,000 loan, the balance is =FV(0.005, 12, 579.98, -30000), which returns about $24,947.

Is it better to use a template or a loan calculator website?

An Excel template gives you a full schedule you can save, edit, and analyze. A website calculator gives a quick number but often doesn’t show the month-by-month breakdown. If you want to track extra payments or plan for refinancing, Excel is more flexible. However, for a simple “how much will my payment be?” question, a website is faster.

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.