Free Excel Loan Amortization Template: Get One Now

How-to basics · Updated November 22, 2026

Yes, you can get a free Excel loan amortization template directly from Microsoft’s official template gallery, and you can also build your own in about five minutes using the built-in PMT function. The Microsoft 365 template called “Loan Amortization Schedule” is free to download, works in Excel for Microsoft 365, Excel 2021, and older versions, and includes a printable schedule plus a summary section. Below, I’ll show you exactly where to find it, how to build a custom one from scratch, and how to avoid common errors that make amortization tables calculate wrong.

Where to Find the Official Free Template

Microsoft maintains a public gallery of free Office templates. To reach the loan amortization one inside Excel:

  1. Open Excel and click File > New.
  2. In the search box, type loan amortization and press Enter.
  3. Look for the template titled Loan Amortization Schedule (it usually has a blue-and-white icon with a table).
  4. Click it, then click Create to download it into a new workbook.

On Excel for the web (free at office.com), the same template is available by going to File > New and searching. On a Mac, the path is identical: File > New from Template, then search. The template includes cells for loan amount, annual interest rate, loan term in years, start date, and number of payments per year. It automatically computes the monthly payment and builds a full schedule with payment number, date, payment amount, interest paid, principal paid, and remaining balance.

How the Template Works Under the Hood

The official template uses three core Excel financial functions: PMT, IPMT, and PPMT. Here is the exact layout it assumes (which you can replicate in a blank sheet):

  • B2 = loan amount (e.g., 25000)
  • B3 = annual interest rate (e.g., 5% entered as 0.05)
  • B4 = loan term in years (e.g., 5)
  • B5 = payments per year (usually 12)

To get the monthly payment, enter this formula in any cell (say B7):

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

That returns 471.78 for a $25,000 loan at 5% over 5 years (60 payments). The negative sign before B2 makes the result positive because Excel treats the loan as money you receive. Then, for the first payment row (say row 10), with period number in A10:

=IPMT($B$3/$B$5, A10, $B$4*$B$5, -$B$2)

That gives the interest portion of payment 1: 104.17. The principal portion is:

=PPMT($B$3/$B$5, A10, $B$4*$B$5, -$B$2)

That returns 367.61. Add them together and you get the full payment of 471.78. The remaining balance after payment 1 is simply the previous balance minus the principal paid. The template fills these formulas down for all periods, and it also handles extra payments if you add a column for them—though the built-in template does not include extra payment logic by default.

How to Choose the Right Template for Your Situation

Not all free templates are equal. Before downloading one, check three things:

  • Does it allow extra payments? If you plan to pay extra each month, you need a template with an “extra payment” column; otherwise the schedule won’t recalculate interest correctly.
  • Is the interest rate entered as a monthly rate or annual? Some templates ask for an annual rate and divide by 12 internally; others expect you to enter the monthly rate. Mixing these up is the #1 cause of wrong results.
  • Does it handle variable rates or only fixed? Most free templates are fixed-rate only. If you have an adjustable-rate mortgage, you’ll need to build a custom schedule or use a paid tool.

For a simple fixed-rate car loan or personal loan, the official Microsoft template is sufficient. For a mortgage with escrow, PMI, or biweekly payments, you’ll likely need a more specialized template—many of which are also free from reputable sites like Vertex42 or Spreadsheet123, but always verify the formulas by spot-checking a few rows against a loan calculator.

Common Problems and How to Fix Them

Even with a good template, users run into the same issues repeatedly:

  1. Interest rate entered as a whole number (5) instead of a decimal (0.05). Fix: divide the rate cell by 100 or format it as a percentage.
  2. Payment shows as a negative number. This is normal if you didn’t use a negative loan amount. To make it positive, add a minus sign in front of the loan amount in the PMT formula.
  3. The schedule doesn’t end at zero. This usually means the payment is rounded. The PMT function returns a long decimal; if you round the payment to two decimals before using it in the schedule, the final balance will be a few cents off. Fix: keep the full precision in the payment cell, or add a final “balloon” adjustment in the last row.
  4. Dates show as serial numbers. Select the date column, press Ctrl+1 (Mac: Cmd+1), and choose a date format.
  5. Extra payments don’t reduce interest. You must recalculate the interest based on the actual remaining balance, not the original schedule. A correct extra-payment template uses the balance from the prior row minus any extra payment before computing interest for the current period.

Building Your Own Simple Template (Step-by-Step)

If you prefer to avoid third-party downloads, you can build a clean, accurate template in under two minutes. Set up your sheet like this:

  • A1: Loan Amount (e.g., 20000)
  • A2: Annual Rate (e.g., 6% as 0.06)
  • A3: Years (e.g., 4)
  • A4: Payments per Year (12)

Then in A6 put the label “Payment Number”, B6 “Payment”, C6 “Interest”, D6 “Principal”, E6 “Balance”. In A7, enter 1. In B7, enter:

=PMT($A$2/$A$4, $A$3*$A$4, -$A$1)

In C7:

=IPMT($A$2/$A$4, A7, $A$3*$A$4, -$A$1)

In D7:

=PPMT($A$2/$A$4, A7, $A$3*$A$4, -$A$1)

In E7, enter the starting balance minus principal paid:

=$A$1 - D7

That returns a balance of 19,552.24 after the first payment on a $20,000 loan at 6% over 4 years (monthly payment is 469.70). Then in A8, enter =A7+1, and copy B7:E7 down to row 55 (for 48 payments). The final balance should be exactly $0.00 (or within a cent). If it isn’t, check that you didn’t round the payment cell.

Free vs. Paid Templates: What’s the Real Difference?

Free templates from Microsoft or reputable sites cover 90% of personal loan needs. Paid templates (often $10–$50) add features like: biweekly payment schedules, adjustable-rate periods, escrow and PMI tracking, payoff comparison charts, and amortization with variable extra payments. If you only need a fixed-rate schedule, paying is unnecessary. If you’re managing a complex mortgage or a business loan with irregular payments, a paid template might save you hours—but you can also achieve most of that with a custom Excel sheet using IF and MIN functions. For example, to cap the final payment so the balance never goes negative, use this in the payment column for the last row:

=MIN(PMT($A$2/$A$4, $A$3*$A$4, -$A$1), E6 + C7)

That ensures the last payment is just enough to clear the remaining balance.

Safety and Accuracy Checks

Before you rely on any amortization template for a real loan decision, verify the numbers independently:

  • Cross-check the first month’s interest manually: balance × monthly rate. For $25,000 at 5% annual, monthly rate is 0.05/12 = 0.004167, so interest = 104.17. If the template shows a different number, something is wrong.
  • Confirm the total interest paid equals (payment × number of payments) – loan amount. For the example above: 471.78 × 60 = 28,306.80; minus 25,000 = 3,306.80 total interest. The template’s sum column should match.
  • Never enter a rate as a percentage without dividing by 100. Use the cell format “Percentage” or enter 0.05.
  • If you plan to make extra payments, always check that the interest for the next period is calculated on the reduced balance, not the original schedule.

These templates are tools for planning, not legal or financial advice. Your actual loan terms—fees, compounding frequency, prepayment penalties—may differ from what a simple schedule shows. Always read your loan contract and confirm with your lender or a qualified financial professional before making decisions based on these numbers.

Frequently Asked Questions

Is the Microsoft loan amortization template really free? Yes, it’s free to download and use in Excel, including Excel for the web with a free Microsoft account. There’s no trial period or hidden cost; it’s part of Microsoft’s public template library.

Does the template work in Google Sheets? The Microsoft template is an .xlsx file that Google Sheets can open, but the formulas may need adjustment because Google Sheets uses the same PMT, IPMT, and PPMT functions with identical syntax. However, the template’s formatting and named ranges might not transfer perfectly. It’s safer to build a simple one in Sheets using the same formulas shown above.

Why does my payment amount differ from a bank’s quote? Banks often include fees, insurance, or use a slightly different rounding method. Also, some loans compound interest daily rather than monthly. The template assumes monthly compounding, which is standard for most personal and auto loans, but not all mortgages. Check your loan agreement for the compounding frequency.

Can I use the template for a mortgage with a 30-year term? Yes, but the schedule will have 360 rows. The official template handles that fine, but it may be slow on very old computers. For a mortgage, also consider that property taxes and insurance are usually escrowed separately—this template only covers principal and interest.

How do I add an extra payment column to the official template? Insert a new column after the “Payment” column, label it “Extra Payment”, and then modify the balance formula to subtract both the principal payment and the extra payment. The interest for the next period will then be calculated on the reduced balance automatically if your balance formula references the prior balance minus total principal reduction.

Is there a way to see a payoff date if I make extra payments? Yes, but the standard template doesn’t do this automatically. You can add a column that checks if the balance reaches zero, and then use a formula to find the first row where that happens. Alternatively, use the NPER function: for a $25,000 loan at 5% with a $500 monthly payment, the number of months is =NPER(0.05/12, -500, 25000), which returns about 55.4 months—so 56 months to pay off.

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.