Excel Loan Amortization Calculator Template

How-to basics · Updated November 22, 2026

The quickest way to get an Excel loan amortization calculator template is to open Excel and go to File > New, then search for “Loan Amortization Schedule” in the template search box. This built-in template is free, fully functional, and works in Excel 2016 and later, including Microsoft 365. If you prefer to build your own from scratch, you can create one in about five minutes using the PMT, IPMT, and PPMT functions — the exact formulas are shown below.

What a Loan Amortization Template Actually Does

An amortization schedule breaks a loan into equal periodic payments and shows, for each payment, how much goes toward interest and how much goes toward the principal. The template calculates the payment amount automatically and then builds a table that lists every payment period (monthly, quarterly, or annually), the interest portion, the principal portion, and the remaining balance after each payment.

The core math relies on a standard financial formula. For a fixed-rate loan, the payment amount is constant, but the split between interest and principal changes over time: early payments are mostly interest, and later payments are mostly principal. This is called “amortization,” and it’s why the balance declines slowly at first.

How to Use the Built-In Excel Template

  1. Open Excel and select File > New.
  2. Type “Loan Amortization” in the search box and press Enter.
  3. Choose the template named “Loan Amortization Schedule” (usually the first result) and click Create.
  4. Enter your loan amount, annual interest rate, number of years, and first payment date in the yellow input cells at the top.
  5. The schedule updates instantly. You can print it or copy it to another sheet.

The template includes a summary section with total interest paid, total cost of the loan, and a chart showing the balance over time. If you want to add extra payments, look for the “Extra Payment” column — the built-in template in Microsoft 365 includes one, but older versions may not.

Building Your Own Amortization Schedule from Scratch

If you want full control or need a custom layout, build your own. The process is straightforward and uses three Excel functions that have been around for decades.

Step 1: Enter loan inputs. Put these values in cells B1 through B4:

  • B1: Loan amount (e.g., 250000)
  • B2: Annual interest rate (e.g., 6% or 0.06)
  • B3: Number of years (e.g., 30)
  • B4: Payments per year (e.g., 12 for monthly)

Step 2: Calculate the payment. In cell B6, enter:

=PMT(B2/B4, B3*B4, -B1)

This returns the periodic payment. For a $250,000 loan at 6% annual interest over 30 years with monthly payments, the result is $1,498.88 (rounded). The negative sign on the loan amount makes the payment positive; Excel’s PMT function returns a negative number by default because it’s a cash outflow.

Step 3: Build the schedule table. In A8, type “Period”; in B8, “Payment”; in C8, “Interest”; in D8, “Principal”; in E8, “Balance”. Then:

  • A9: 1 (the first period)
  • B9: =$B$6 (the fixed payment)
  • C9: =E8*$B$2/$B$4 (interest for the period; assumes E8 holds the starting balance, so put the initial loan amount in E8 before copying formulas)
  • D9: =B9-C9 (principal portion)
  • E9: =E8-D9 (new balance)

Before copying down, put the initial loan amount in E8 (e.g., =B1). Then select A9:E9 and drag the fill handle down to row 368 (for 360 monthly payments plus the initial row). The final balance should be $0.00 (or within a few cents due to rounding).

For the interest and principal for a specific period, use IPMT and PPMT:

=IPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)
=PPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)

These return the interest and principal for period A9. They are useful if you only need one period’s breakdown without building the whole table.

How to Choose the Right Template for Your Needs

Not all amortization templates are the same. Before downloading one from the internet, check for these features:

  • Extra payment support: Does it allow irregular extra payments, or only fixed monthly additions? Some templates only handle a constant extra amount.
  • Variable rate support: Most templates are fixed-rate only. If you have an adjustable-rate mortgage, you need a template that allows rate changes at specific periods.
  • Payment frequency: Monthly is standard, but some loans are bi-weekly or quarterly. Make sure the template matches your loan terms.
  • Charts and summaries: A good template shows total interest, payoff date, and a balance chart. These help you compare scenarios.
  • No macros: Prefer templates without VBA macros unless you know the source is trustworthy. Macros can contain harmful code and cannot be undone.

If you only need a quick estimate, the built-in Excel template is fine. If you’re comparing multiple loan offers, build your own or use a template with a “scenario” section.

Common Problems and How to Fix Them

Even with a good template, users often hit the same issues. Here are the most frequent ones and their fixes.

Payment shows as negative. This is normal — Excel’s financial functions return negative values for outflows. To display a positive number, either negate the formula (e.g., =-PMT(...)) or format the cell to show positive numbers as black.

Final balance isn’t zero. This happens because of rounding. The PMT function returns a value with many decimal places, but the schedule may round each payment to cents. To fix, either keep full precision in the payment cell (don’t round) or adjust the last payment to clear the remaining balance. In practice, lenders adjust the final payment.

Interest calculation is wrong for the first period. If your first payment is not exactly one period after the loan start, you need to adjust the interest formula. For example, if you take a loan on March 15 and the first payment is May 1, the first interest period is 47 days, not one month. Use =E8*$B$2/365*47 for that first row, then switch to the normal formula.

Template won’t recalculate. Check that calculation mode is set to Automatic: go to Formulas > Calculation Options > Automatic. If it’s on Manual, press F9 to recalculate.

Comparing Excel Templates vs. Online Calculators vs. Google Sheets

Excel templates are not the only option. Online calculators (like those on bank websites) are convenient but often don’t let you save or edit the schedule. Google Sheets has a free template gallery with a similar amortization sheet, and the formulas are nearly identical — the main difference is that Google Sheets uses PMT, IPMT, and PPMT with the same syntax, but the menu paths differ (e.g., File > New > From template gallery).

Excel’s advantage is full control: you can add conditional formatting, create custom charts, and use modern functions like SEQUENCE to generate the period numbers dynamically. For example, in Excel 365 you can replace the manual fill with:

=SEQUENCE(360,1,1,1)

in A9, and it will spill 360 periods automatically. This function is not available in Excel 2019 or earlier.

If you’re comparing loans, a spreadsheet lets you change the rate or term and instantly see the impact on total interest. Online calculators often limit you to one scenario at a time.

Safety, Accuracy, and Financial Advice Disclaimer

Amortization templates are mathematical tools, not financial advice. The numbers they produce are only as accurate as the inputs. Always verify the interest rate, term, and payment frequency with your lender. For adjustable-rate loans, the schedule will be inaccurate after the rate changes unless you adjust the formulas.

Be cautious with templates downloaded from random websites. They may contain macros that could harm your computer. If you download a macro-enabled file (.xlsm), only open it if you trust the source. Before running any VBA macro, save a copy of your workbook — macros cannot be undone with Ctrl+Z.

For tax purposes, the interest amounts shown in your schedule are estimates. Lenders provide official Form 1098 or year-end statements that you should use for tax filings. This article does not constitute financial, tax, or legal advice; consult a qualified professional for your specific situation.

Frequently Asked Questions

Can I use an Excel template for a car loan or personal loan? Yes. The same schedule works for any fixed-rate, fixed-term loan. Just change the loan amount, rate, and term. The payment frequency should match your loan’s payment schedule (usually monthly).

How do I add extra payments to the built-in template? In the Microsoft 365 template, there is an “Extra Payment” column. Enter the extra amount in the row for the period you want to make it. The template recalculates the balance and the payoff date. If your template lacks this column, you can add it manually, but you’ll need to modify the balance formula to subtract the extra payment.

Why does my interest payment decrease over time? Because the principal balance decreases with each payment. Interest is calculated on the remaining balance, so as you pay down the principal, the interest portion shrinks. The payment stays the same, so more of each payment goes to principal.

What is the difference between PMT, IPMT, and PPMT? PMT returns the total periodic payment. IPMT returns the interest portion for a specific period. PPMT returns the principal portion for a specific period. For a given period, IPMT + PPMT = PMT.

Can I use an amortization template for a variable-rate loan? Not directly. A standard template assumes a fixed rate. To handle a variable rate, you need to adjust the interest formula each time the rate changes. You can do this by adding a column for the rate and using a formula like =E8*rate/12 where “rate” changes per row. Some advanced templates include this feature, but they are less common.

Does Excel for Mac have the same amortization template? Yes, the built-in template gallery is available in Excel for Mac. The steps are the same: go to File > New from Template, search for “Loan Amortization,” and select it. The formulas and functions work identically.

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.