Free Excel templates

A free loan amortization schedule in Excel, with extra payments

Payment, a 360-month schedule of interest and principal, total interest, the payoff month with an extra monthly payment, and the interest it saves: four sheets, no macros.

The file amortizes a 400,000 loan at a fixed 6.50% over 30 years. The monthly payment is 2,528.27, and 85.7% of the first one is interest. Over the full term the borrower pays 510,178 of interest, more than the loan itself. Adding 200 a month of extra principal pays the loan off in 293 months instead of 360, 67 months early, and saves 111,892 of interest.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Loan_Amortization_Schedule.xlsx (49 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with Real Estate Finance, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Four sheets. Inputs are blue cells on a pale blue fill on the Inputs sheet.

Read me
What the file does and its conventions.
Inputs
Loan amount, rate, term, first payment date and the extra monthly principal, then the monthly rate, number of payments and the payment.
Schedule
360 months: date, opening balance, payment, interest, principal, extra principal and closing balance, stopping when the loan is repaid.
Summary and Checks
Total interest with and without the extra payment, months and interest saved, payoff date, balance after five years; then the checks: all OK.

What the worked case returns

MeasureValue
Monthly payment2,528.27
Interest share of the first payment85.7%
Total interest, no extra payment510,178
Total interest with 200 a month extra398,286
Interest saved111,892
Months to pay off with the extra293 (67 saved)
Balance after five years360,309

The extra payment works because it goes entirely to principal, and every dollar of principal repaid early stops earning interest for the rest of the loan. Early in the schedule, when interest is most of each payment, 200 a month is a large share of the principal actually being repaid.

How to use it on your own loan

  1. Enter the loan amount, rate, term and first payment date on Inputs.
  2. Set the extra monthly principal, or 0 for the contractual schedule.
  3. Read the payment and the schedule.
  4. Read the Summary for total interest, payoff date and interest saved.

Questions people ask about it

Is this template really free?

Yes. The file costs nothing: you enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference: PMT for the payment, then one row a month.

How is the monthly payment calculated?

With the annuity formula Excel calls PMT: the loan times the monthly rate, divided by one minus one plus the monthly rate to the power of minus the number of payments. Here 2,528.27.

Does it handle variable rates?

No. The rate is fixed for the whole term. For a variable rate, change the rate column month by month on the Schedule sheet.

Go further

For how lenders size and price the loans behind schedules like this one, Real Estate Finance and its companion workbooks go further.

See the book and its free files →

Other free templates

This template goes with Real Estate Finance. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.