Skip to content
LoveToCalc

Loan amortization schedule in Excel

A ready-made amortization schedule as an Excel file, with example numbers and live formulas. Type your own loan into the blue cells and every month recalculates.

The sheet

#ABCDE
1Change the numbers in the blue cells and the sheet recalculates.
2Loan amount25,000
3Interest rate7.00%
4Loan term (years)5
5RepaymentEqual paymentsType one of: Equal payments / Equal principal
6
7MonthPaymentInterestPrincipalBalance left
81495.03145.83349.2024,650.80
92495.03143.80351.2324,299.57
103495.03141.75353.2823,946.29
114495.03139.69355.3423,590.94
125495.03137.61357.4223,233.53
136495.03135.53359.5022,874.03
147495.03133.43361.6022,512.43
158495.03131.32363.7122,148.72
169495.03129.20365.8321,782.89
1710495.03127.07367.9621,414.93
1811495.03124.92370.1121,044.82
1912495.03122.76372.2720,672.55
The first rows of loan-amortization-schedule.xlsx, as the file holds them. Change the blue cells and the rest follows. The sheet goes on to row 607.

Download loan-amortization-schedule.xlsxExcel workbook, 30 kB

The formulas are written in Excel’s own syntax and checked in LibreOffice Calc and Apple Numbers, where they recalculate to the calculator’s numbers.

How the sheet calculates

The blue cells

B2
Loan amount
25,000
B3
Interest rate
7.00%
B4
Loan term (years)
5
B5
Repayment
Equal payments

The columns

The formulas are shown as the file holds them, in Excel’s English syntax. Excel set to Swedish shows them translated, with semicolons between the arguments: PMT is BETALNING.

What is in the file

The sheet has five rows of inputs at the top and then one row per month: payment, interest, principal and the balance still owed. The example is a loan of 25,000 at 7% over 5 years with equal payments, which is 495.03 a month and 4,701.80 in interest all told. None of it is typed in as a number. Every cell of the table is a formula pointing at the blue cells, so the example disappears the moment you enter your own loan.

It is a plain .xlsx with no macros, so there is no macro warning to click through. It opens in Excel, Google Sheets, Numbers and LibreOffice.

Changing the numbers

The blue cells are the only ones you need to touch:

Switching from equal payments to equal principal changes the principal column from an amount that grows each month to the same amount all the way, and the payment from a fixed amount to one that falls. For the example the payment goes from 562.50 in the first month to 419.10 in the last, and the interest comes to 4,447.92 instead of 4,701.80.

Shorten the term and the months after the new end leave the table; lengthen it and they come back. The rows are already there, holding formulas that show nothing while their month lies beyond the term.

The formulas in the cells

A month’s interest is what was left after the previous month, times the annual rate divided by twelve. The principal depends on the repayment type: with equal payments it is the month’s whole payment, which Excel finds with PMT, minus the interest; with equal principal it is the amount divided by the number of months. The payment is interest plus principal, and the balance is last month’s balance minus the principal.

The formulas are listed above, column by column, as the file holds them. A localized Excel shows the same formulas with its own function names the moment the file opens.

The outer condition is what makes the term live: month 1 shows only if 1 ≤ the term in months, month 2 only if 2 ≤ the term, and so on to month 600.

Checked in three programs

Every time the export changes, the file is recalculated and compared with our own engine in Excel’s syntax, in LibreOffice and in Apple Numbers. A sheet doing the same thing in all three is not a given: Numbers lacks some functions Excel has, and the first version of this sheet used one of them, which left a column frozen in Numbers. So the check opens the file for real in each and compares every cell.

Opening it in Google Sheets, Numbers or LibreOffice

Want your own numbers right away?

The loan calculator runs the same schedule on the page, compares two rates or terms side by side, and downloads that exact schedule as Excel with your numbers already in the blue cells.

Frequently asked questions

Why do the columns show an error after I typed in B5?

The sheet knows only the two labels beside the cell, Equal payments and Equal principal. Any other word, or a typo, leaves the principal empty, and the payment and balance then show an error rather than guessing. Fix the word and the error goes.

Why is the last row not exactly 0?

Excel works with many decimals, and the last row can end on an amount that is zero to the eighth decimal but shows as −0.00 or 0.00. That is rounding, not debt.

Can I add fees?

Not in this template. The total loan cost calculator takes an arrangement fee and a monthly fee and downloads its schedule the same way, with the fees in a column of their own.

Does it work for a mortgage?

Yes, the schedule is the same: a mortgage is a loan with a long term, and the sheet has room for 50 years. What it does not do is a repayment requirement that steps down as the loan-to-value falls; that needs a rule set for the country, and the template stays rule-free.

Your own numbers? Use the calculator: Loan calculator.

Last reviewed October 6, 2026 ·How pages are reviewed