Free Loan Amortization Schedule Template (Excel and Google Sheets)

Before you take a car loan, personal loan, business loan or mortgage, it helps to see exactly where your money goes. This free amortization schedule shows every payment: how much is interest, how much reduces the loan, and the balance left. It also shows how much you’d save by paying a little extra each month.

Download the free loan amortization schedule (.xlsx)

Loan Amortization Schedule template preview with sample data
Preview of the Loan Amortization Schedule template with sample data. Download it above and replace the examples with your own.

What is inside

Feature What it does
Loan inputs Amount, annual interest rate, payments per year (monthly, quarterly, half-yearly or yearly), first payment date and term
Automatic payment Calculates the fixed payment with Excel’s PMT function
Full schedule Every payment with date, opening balance, interest, principal, closing balance and cumulative interest (up to 360 payments)
Extra payments Add an extra amount per period and see the new payoff date
Summary Total interest, total paid, actual number of payments, payoff date and interest saved

Worked example

A 20,000 loan at 6.5% a year, paid monthly over 5 years:

No extra payment With 100 extra each month
Monthly payment 391.323 491.323
Number of payments 60 47
Total interest 3,479.378 2,655.512
Interest saved — 823.865

In the first month, 108.333 of the payment is interest (20,000 × 6.5% ÷ 12) and 282.990 reduces the loan. By the last payment, almost all of it goes to the loan. That’s how amortization works.

How to use it

  1. Enter the loan amount, annual interest rate and term in years.
  2. Choose payments per year (12 for monthly) and the first payment date.
  3. Read the scheduled payment and the summary at the top.
  4. Optional: try an extra payment to see how much time and interest you save.
  5. Print it or save as PDF to compare offers from different banks.

The formula behind it

=PMT(annual_rate/12, years*12, -loan_amount)

Each month: interest = opening balance × rate ÷ 12, principal = payment − interest, and closing balance = opening balance − principal. Use Goal Seek to work backwards, for example to find the loan you can afford for a given monthly payment.

Compare loan offers

  • Make a copy of the sheet for each offer and compare total interest, not just the monthly payment.
  • A longer term lowers the payment but usually costs more interest overall.
  • Ask the bank about fees and early-repayment charges, which this schedule doesn’t include.

Plan how the payment fits your budget with the monthly budget template or, for a business, the cash flow forecast.

Important note

This template uses standard fixed-rate amortization for planning. Real loans may calculate interest daily, round payments, use variable rates or add fees, and Islamic financing uses different structures. Always check the official schedule from your bank. This is not financial advice.

FAQ

How do I calculate a loan payment in Excel?

Use =PMT(rate/12, years*12, -amount). For example, =PMT(6.5%/12, 60, -20000) returns about 391.32 per month.

Does it work in Google Sheets?

Yes. Download the file and import it with File → Import → Upload. PMT and EDATE work the same in Google Sheets.

How do extra payments help?

Extra payments go straight to the loan balance, so later interest is calculated on a smaller amount. In the example, 100 extra a month saves about 824 in interest and ends the loan 13 months early.

Can I use it for a mortgage?

Yes, for fixed-rate planning up to 30 years of monthly payments. Check your lender’s schedule for fees, rounding and variable rates.

Leave a Comment