Template · Finance
Loan amortization schedule
Monthly payment, interest and principal for every period of a fixed-rate loan, with optional extra payments.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Loan amortization schedule | ||
| Example data — replace the blue input cells with your own. | ||
| Loan inputs | ||
| Loan amount | $350,000.00 | Amount borrowed |
| Annual interest rate | 6.50% | Nominal annual rate |
| Term (years) | 30 | |
| Payments per year | 12 | 12 = monthly, 4 = quarterly, 26 = every two weeks, 52 = weekly |
| First payment date | Nov 1, 2026 | |
| Extra payment per period | $200.00 | Paid on top of every scheduled payment; enter 0 for none |
| Summary | ||
| Periodic interest rate | 0.54% | Annual rate ÷ payments per year |
| Number of scheduled payments | 360 | |
| Scheduled payment | $2,212.24 | PMT(periodic rate, number of payments, -loan amount) |
| Total of scheduled payments | $796,405.71 |
Showing the first 16 of 34 rows and 3 of 3 columns. Cells with formulas show the formula on hover.
| Amortization schedule | ||||||||
| One row per payment; rows after the loan is repaid stay blank. All cells are formulas — change the inputs on the Inputs tab. | ||||||||
| Period | Payment date | Opening balance | Scheduled payment | Extra payment | Interest | Principal | Closing balance | Cumulative interest |
| 1 | Nov 1, 2026 | $350,000.00 | $2,212.24 | $200.00 | $1,895.83 | $516.40 | $349,483.60 | $1,895.83 |
| 2 | Dec 1, 2026 | $349,483.60 | $2,212.24 | $200.00 | $1,893.04 | $519.20 | $348,964.39 | $3,788.87 |
| 3 | Jan 1, 2027 | $348,964.39 | $2,212.24 | $200.00 | $1,890.22 | $522.01 | $348,442.38 | $5,679.09 |
| 4 | Feb 1, 2027 | $348,442.38 | $2,212.24 | $200.00 | $1,887.40 | $524.84 | $347,917.54 | $7,566.49 |
| 5 | Mar 1, 2027 | $347,917.54 | $2,212.24 | $200.00 | $1,884.55 | $527.68 | $347,389.85 | $9,451.04 |
| 6 | Apr 1, 2027 | $347,389.85 | $2,212.24 | $200.00 | $1,881.70 | $530.54 | $346,859.31 | $11,332.74 |
| 7 | May 1, 2027 | $346,859.31 | $2,212.24 | $200.00 | $1,878.82 | $533.42 | $346,325.89 | $13,211.56 |
| 8 | Jun 1, 2027 | $346,325.89 | $2,212.24 | $200.00 | $1,875.93 | $536.31 | $345,789.59 | $15,087.49 |
| 9 | Jul 1, 2027 | $345,789.59 | $2,212.24 | $200.00 | $1,873.03 | $539.21 | $345,250.38 | $16,960.52 |
| 10 | Aug 1, 2027 | $345,250.38 | $2,212.24 | $200.00 | $1,870.11 | $542.13 | $344,708.24 | $18,830.62 |
| 11 | Sep 1, 2027 | $344,708.24 | $2,212.24 | $200.00 | $1,867.17 | $545.07 | $344,163.17 | $20,697.79 |
| 12 | Oct 1, 2027 | $344,163.17 | $2,212.24 | $200.00 | $1,864.22 | $548.02 | $343,615.15 | $22,562.01 |
Showing the first 16 of 365 rows and 9 of 9 columns. Cells with formulas show the formula on hover.
| Loan amortization schedule |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Builds a full payment schedule for a fixed-rate loan such as a mortgage, car loan or personal loan, and shows how an extra payment each period shortens the loan and cuts interest. |
| How to use it |
| 1. Enter the loan amount, annual interest rate, term in years, payments per year and first payment date on the Inputs tab. |
| 2. Optionally enter an extra payment per period (enter 0 for none). |
| 3. Read the scheduled payment, total interest, payoff dates and interest saved in the Summary block. |
| 4. Open the Schedule tab to see every payment. Rows after the loan is repaid stay blank. |
| 5. Use the cross-check block to compare any period with Excel's IPMT and PPMT functions. |
| Formulas and method |
| Periodic rate: annual rate ÷ payments per year. Number of payments: years × payments per year. |
| Scheduled payment: PMT(periodic rate, number of payments, -loan amount). |
Showing the first 16 of 33 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook shows how a fixed-rate loan is repaid over time. Enter the loan amount, the annual interest rate, the term, the payments per year and the first payment date. The Inputs tab calculates the scheduled payment with PMT, and the Schedule tab splits every payment into interest and principal, tracks the balance period by period and shows the date of each payment.
An optional extra payment shortens the loan. The summary block compares total interest with and without extra payments, and it shows the new payoff date and the interest saved. A final-period guard clears the balance exactly, so the last row never shows a negative balance. A cross-check block compares any period with the IPMT and PPMT functions.
The schedule has room for 360 payments, enough for a 30-year monthly loan. The example is a fictional $350,000 loan at 6.5% over 30 years.
What’s inside
- Payment computed with PMT from the periodic rate and the number of payments
- Each row splits the payment into interest and principal, with the balance after it
- Optional extra payment per period, with the interest saved and the new payoff date
- Final period clears the balance exactly, so the balance never goes negative
- IPMT and PPMT cross-check for any period, across 360 schedule rows
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Inputs | Loan amount, rate, term, first payment date and extra payment, with the summary and cross-check. |
| Schedule | One row per payment: date, opening balance, interest, principal and closing balance. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 3,263 formulas in 3,351 cells across 3 tabs, so 97% of its cells calculate. They use 15 distinct functions; the longest formula is 156 characters and 1,807 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 4,329 | one result when a test is true, another when false |
OR | 363 | true when any test is true |
EDATE | 361 | same day, months later |
MOD | 361 | remainder after division |
ROUND | 361 | rounds to a number of digits |
MIN | 360 | smallest value |
AND | 359 | true when every test is true |
SUM | 7 | adds numbers |
INDEX | 2 | value at a position in a range |
ABS | 1 | absolute value |
COUNT | 1 | counts numeric cells |
IPMT | 1 | interest part of a payment |
The 12 most used of 15 functions. Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Inputs tab, enter the loan amount, annual interest rate, term in years and payments per year.
- Enter the first payment date, and an extra payment per period or 0 for none.
- Read the scheduled payment, total interest and payoff dates in the summary block.
- Open the Schedule tab to see every payment, its interest, principal and closing balance.
- Use the cross-check block to compare one period with IPMT and PPMT.
What is it good for?
- Comparing a 15-year and a 30-year mortgage
- Seeing how an extra $200 a month shortens a car loan
- Checking a lender's payment and total interest
- Planning the payoff date of a student or personal loan
Questions about this sheet
Does the sheet handle extra payments?
Yes. Enter an extra payment per period on the Inputs tab. The schedule applies it to principal each period, and the summary shows the interest saved and the new payoff date.
Why can the last payment differ from the others?
In the final period the principal is set to the remaining balance, so the balance closes at exactly zero. A lender that rounds each payment to the cent may show a difference of a few cents.
Can it model a loan whose rate changes?
No. The schedule uses one fixed annual rate for the full term. An adjustable-rate loan needs a new schedule from each rate change.
How many payments does it cover?
Up to 360 payments. A longer term is flagged by the schedule check on the Inputs tab.
Does the payment include taxes and insurance?
No. The scheduled payment is principal and interest only, so escrow for taxes and insurance is not included.