calculate-loan

01 Loan payments

Free Amortization Excel Spreadsheet? Yes, Here’s How

Calculator No. 01-23273 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

Yes, you can get a free amortization spreadsheet without paying for premium templates or software. The simplest route is to build one yourself in Excel or Google Sheets using the built-in PMT function, or download a free template from Microsoft’s official template gallery. This article explains what an amortization schedule is, how to create one from scratch, what to look for in a free template, and common mistakes to avoid.

Formula

The standard amortization formula calculates the fixed monthly payment (PMT) required to pay off a loan over a set number of periods at a fixed interest rate:

PMT = P × (r × (1 + r)^n) / ((1 + r)^n – 1)

  • P = principal (loan amount) in dollars
  • r = monthly interest rate (annual rate divided by 12, expressed as a decimal; e.g., 8% APR → 0.08/12 = 0.0066667)
  • n = total number of payments (loan term in years × 12)

In Excel, this is the PMT(rate, nper, pv) function, where rate is the monthly rate, nper is the number of months, and pv is the present value (the loan amount, entered as a negative number).

Worked Example

Using the default values from the calculator above: loan amount $20,000, APR 8%, term 5 years (60 months).

  1. Monthly rate r = 8 / 1200 = 0.0066667
  2. Number of payments n = 5 × 12 = 60
  3. Payment = 20000 × (0.0066667 × (1.0066667)^60) / ((1.0066667)^60 – 1)
  4. (1.0066667)^60 ≈ 1.48985
  5. Numerator = 20000 × (0.0066667 × 1.48985) ≈ 20000 × 0.0099323 = 198.646
  6. Denominator = 1.48985 – 1 = 0.48985
  7. Payment ≈ 198.646 / 0.48985 ≈ 405.53

So the monthly payment is $405.53. Total paid = 405.53 × 60 = $24,331.80, and total interest = 24,331.80 – 20,000 = $4,331.80. These match the calculator outputs.

Assumptions

  • Fixed interest rate for the entire term (no rate changes).
  • Payments are made at the end of each month (ordinary annuity).
  • Interest is compounded monthly at APR/12.
  • The calculation does not include fees, taxes, insurance, PMI, or escrow amounts.

How an Amortization Schedule Works

An amortization schedule is a table that breaks down each payment into two parts: interest and principal. Early in the loan, a larger share goes to interest; over time, the principal portion grows. This happens because interest is calculated on the remaining balance, which shrinks as you pay down principal.

For example, in the first month of the $20,000 loan above, interest is 20,000 × 0.0066667 = $133.33, so principal reduction is 405.53 – 133.33 = $272.20. The new balance is $19,727.80. In the last month, interest is only about $2.68, and principal is $402.85.

To build this in Excel manually, you can create columns for Payment Number, Payment, Interest, Principal, and Balance. Use formulas like =P*r for interest and =Pmt - Interest for principal, then subtract from the previous balance. Alternatively, use Excel’s built-in IPMT and PPMT functions to get interest and principal for any period.

Where to Find Free Templates

Several reliable sources offer free amortization templates:

  • Microsoft Office Templates – The official template gallery includes a simple amortization schedule that works in Excel. Search “amortization” at templates.office.com.
  • Google Sheets Template Gallery – Google Sheets has a built-in “Loan Amortization” template that you can copy to your Drive for free.
  • Your own spreadsheet – Building one takes about 10 minutes and gives you full control. You only need the PMT function and a few formulas.

Be cautious with third-party sites that require an email or payment. Many “free” downloads are actually lead magnets for paid services. Always download from a site you trust, and check the file for macros before enabling them.

Choosing the Right Free Spreadsheet

When evaluating a free amortization template, consider these features:

  • Extra payments – Does it allow you to add extra principal payments? This is a common need for people who want to pay off a loan faster.
  • Variable rates – Some templates support adjustable rates, but most are fixed-rate only.
  • Printable summary – A clear summary showing total interest and payoff date is helpful.
  • No macros – Macros can be security risks; a simple formula-based sheet is safer and works on mobile versions of Excel.
  • Compatibility – If you use Google Sheets, ensure the template is compatible or convert it easily.

For most users, the built-in Google Sheets template or a simple self-built sheet is sufficient. If you need extra payments, you may need to add a column or use a more advanced template.

Common Mistakes and How to Avoid Them

  • Using annual rate instead of monthly – Always divide the APR by 12. Forgetting this will give a wildly wrong payment.
  • Not rounding the payment – In real life, your payment is rounded to the cent. If your spreadsheet uses unrounded values, the final balance may be off by a few cents. Round the payment to 2 decimals and recalculate interest on the rounded payment.
  • Ignoring extra payments – If you make extra payments, the schedule changes. Many free templates don’t handle this automatically.
  • Confusing APR with nominal rate – APR includes certain fees, but the monthly interest rate is still APR/12. For a true APR, the loan’s finance charges are factored into the rate, but the amortization math remains the same.
  • Forgetting that the first payment may be partial – If your first payment is not exactly one month after disbursement, the schedule will be slightly different. Most templates assume a full month.

Comparing Excel vs. Online Calculators

An Excel spreadsheet gives you full control and offline access. You can customize columns, create charts, and keep a permanent record. Online calculators (like the one on this page) are quicker for one-off estimates and require no setup. Both use the same formula, so results are identical if inputs match. The main advantage of a spreadsheet is flexibility: you can model extra payments, compare scenarios side by side, and save your data. The advantage of an online calculator is convenience and the fact that you don’t need to build or download anything.

Frequently Asked Questions

Is there a free amortization template in Excel? Yes, Microsoft offers a free “Loan Amortization Schedule” template in the Office template gallery. You can also create one yourself using the PMT function in under five minutes.

How do I add extra payments to an amortization schedule? In a basic schedule, you can add a column for “Extra Payment” and subtract it from the balance after the regular principal payment. For a fully automatic schedule, you may need a more advanced template or a custom formula that recalculates the remaining term.

What is the difference between APR and interest rate? The interest rate is the cost of borrowing the principal, while APR includes certain fees and costs, expressed as a yearly rate. For amortization calculations, you use the monthly rate derived from the APR (APR/12) to compute payments.

Can I use a free spreadsheet for a mortgage or auto loan? Yes, any amortization spreadsheet works for any fixed-rate loan, including mortgages, auto loans, personal loans, and student loans. Just enter the correct loan amount, APR, and term. For mortgages, remember that property taxes and insurance are usually not included in the amortization schedule.

Why does my spreadsheet show a slightly different total interest than a bank’s quote? Banks may round the payment differently, charge a daily interest for the first partial month, or include fees. The spreadsheet gives the mathematical result for a standard monthly payment schedule; the lender’s disclosure will show the exact figures.

Is it safe to download a free amortization template from the internet? Generally, yes if you download from reputable sources like Microsoft or Google. Avoid files from unknown sites that prompt you to enable macros, as they could contain malicious code. When in doubt, build your own.

The result from any amortization calculator is an estimate; your lender’s official disclosure provides the exact payment and total interest for your specific loan.

Related

Formula used
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

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →