Home › Templates › Loan amortization schedule
Loan Amortization Schedule (Excel Template)
A free loan amortization schedule for Excel and Google Sheets: type the loan amount, rate, term and first payment date, choose monthly, biweekly or weekly payments, add a fixed or one-off extra payment, and the sheet builds every payment row with its date, principal, interest and balance, plus your payoff date, total interest, interest saved and time saved.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no email gate, no add-on.
| No. | Payment date | Beginning balance | Payment | Principal | Interest | Extra | Ending balance | Cumulative interest |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 Dec 2026 | $250,000.00 | $1,580.17 | $226.00 | $1,354.17 | $200.00 | $249,574.00 | $1,354.17 |
| 2 | 1 Jan 2027 | $249,574.00 | $1,580.17 | $228.31 | $1,351.86 | $200.00 | $249,145.69 | $2,706.03 |
| 3 | 1 Feb 2027 | $249,145.69 | $1,580.17 | $230.63 | $1,349.54 | $200.00 | $248,715.06 | $4,055.57 |
| 4 | 1 Mar 2027 | $248,715.06 | $1,580.17 | $232.96 | $1,347.21 | $200.00 | $248,282.10 | $5,402.78 |
| 5 | 1 Apr 2027 | $248,282.10 | $1,580.17 | $235.31 | $1,344.86 | $200.00 | $247,846.79 | $6,747.64 |
| 6 | 1 May 2027 | $247,846.79 | $1,580.17 | $237.67 | $1,342.50 | $200.00 | $247,409.12 | $8,090.14 |
| … rows 7 to 262 … | ||||||||
| 263 | 1 Oct 2048 | $4,789.33 | $1,580.17 | $1,554.23 | $25.94 | $200.00 | $3,035.10 | $221,219.81 |
| 264 | 1 Nov 2048 | $3,035.10 | $1,580.17 | $1,563.73 | $16.44 | $200.00 | $1,271.37 | $221,236.25 |
| 265 | 1 Dec 2048 | $1,271.37 | $1,278.26 | $1,271.37 | $6.89 | $0.00 | $0.00 | $221,243.14 |
| Rows 266 to 480 stay blank: the loan is paid off. | ||||||||
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, how the maths works, colour key, limits, disclaimer |
| Loan | Loan amount, annual rate, term, Years or Months, Monthly / Biweekly / Weekly, first payment date, fixed extra per payment (optional), up to 24 one-off extra payments by payment number | Input check, payments per year, rate per payment, number of payments, scheduled payment (PMT); payoff date, number of payments, total interest and total paid with and without extra; interest saved; time saved; a year-by-year table; balance line chart; principal vs interest column chart |
| Schedule | Nothing | Up to 480 rows: payment date, beginning balance, payment, principal, interest, extra, ending balance, cumulative interest; rows after payoff stay blank. Columns K:L rerun the loan with no extra payments for the comparison |
| Example Loan, Example Schedule | Already filled | The fictional $250,000 loan above, so you can see every formula working before you type your own numbers |
The same sheet works for a mortgage, an auto loan or a personal loan, as long as the rate is fixed and payments are equal. To compare loan offers by their total cost including fees, use the APR calculator; for several debts at once, use the debt payoff spreadsheet.
How to use it
- Open Loan. Type the loan amount, annual interest rate (for example 6.5%), term and the date of your first payment in the yellow cells. Use the numbers from your loan estimate, note or statement.
- Pick the term unit (Years or Months, so a 72-month auto loan can be typed as 72) and the payment frequency (Monthly, Biweekly or Weekly).
- Check that
B11says OK. If not, it tells you which input to fix. - Optional: type a fixed extra amount you plan to pay every period in
B10. For a one-off extra payment (a bonus, a tax refund), type its payment number and amount in the table atF5:G28; column H shows the date of that payment. - Read the Summary: scheduled payment, payoff date with and without extra, number of payments, total interest, interest saved and time saved.
- Open Schedule to see every payment. Compare a row with your lender's statement to confirm the sheet matches your loan.
Tip: to see how much a one-off payment would save, type it, read Interest saved, then delete it. Nothing else changes.
How amortization works
An amortizing loan has an equal payment every period. Part of it pays interest, the rest pays down the balance. The CFPB's page "How does paying down a mortgage work?" puts it this way: "In the beginning of your mortgage term, you owe more interest, because your loan balance is still high. Most of your monthly payment is applied to the interest you owe, and the remainder is applied to paying off the principal." Over time the balance falls, so the interest share falls and the principal share grows. "This process is known as amortization."
The payment itself is set so the loan ends on time. The CFPB's "How do mortgage lenders calculate monthly payments?" says a typical fixed-rate mortgage "is calculated so that if you keep the loan for the full loan term – for example, 30 years – and make all of your payments, you will precisely pay off the loan at the end of the loan term." That is what Excel's and Google Sheets' PMT function returns.
In the example without extra payments, year 1 is $16,167.73 of interest and $2,794.31 of principal. Principal first exceeds interest at payment 233 (1 Apr 2046), more than 19 years in. After five years the balance is still $234,027.48 of the original $250,000.
What a loan payment does not include
This schedule covers principal and interest only. For a mortgage, your actual monthly bill is usually higher:
- The CFPB's "What is PITI?": "Principal, Interest, Taxes, and Insurance, known as PITI, are the four basic elements of a monthly mortgage payment." Taxes and homeowner's insurance "may go into an escrow account".
- The CFPB's monthly-payments page (linked above) adds: "The total monthly payment you send to your mortgage company is often higher than the principal and interest payment explained here."
- Microsoft's PMT function documentation: "The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans."
Private mortgage insurance (PMI), HOA dues and loan fees are also outside the schedule. Adjustable rates are not modelled: the CFPB notes that when an ARM's rate adjusts, the payment "will typically (though not always) be re-calculated based on the new interest rate and the remaining loan term." For an ARM, use the sheet for the current fixed period only, then start a new schedule from the balance on the reset date.
Before you pay extra: the CFPB's "What is a prepayment penalty?" explains that some lenders charge a fee "if you pay off all or part of your mortgage early", adding that penalties "do not normally apply if you pay extra principal on your mortgage in small chunks at a time–but it’s always a good idea to double check with the lender." The sheet assumes every extra dollar reduces principal straight away.
The formulas, explained
Exact formulas from the file. Schedule row 9 is payment 2 (row 8, payment 1, starts from the loan amount instead of the row above). On Loan, B4 is the loan amount, B5 the annual rate, B6 the term, B7 the unit, B8 the frequency, B9 the first payment date, B10 the fixed extra, and F5:G28 the one-off extras.
| Field | Formula |
|---|---|
| Payments per year (Loan!B14) | =IF(B8="Weekly",52,IF(B8="Biweekly",26,12)) |
| Rate per payment (Loan!B15) | =N(B5)/B14 |
| Number of payments (Loan!B16) | =IF(N(B6)<=0,0,ROUND(IF(B7="Months",B6/12,B6)*B14,0)) |
| Scheduled payment, PMT (Loan!B17) | =IF(OR(N(B4)<=0,B16<1),0,IF(B15=0,ROUND(B4/B16,2),ROUND(PMT(B15,B16,-B4),2))) |
| Payment no. (Schedule!A9) | =IF(OR(2>Loan!$B$16,N(H8)<=0),"",2) |
| Payment date (B9) | =IF(OR(A9="",Loan!$B$9=""),"",IF(Loan!$B$14=12,EDATE(Loan!$B$9,A9-1),Loan!$B$9+(A9-1)*IF(Loan!$B$14=26,14,7))) |
| Beginning balance (C9) | =IF(A9="","",H8) |
| Payment (D9) | =IF(A9="","",IF(OR(A9=Loan!$B$16,C9+F9<=Loan!$B$17),C9+F9,Loan!$B$17)) |
| Principal (E9) | =IF(A9="","",D9-F9) |
| Interest (F9) | =IF(A9="","",ROUND(C9*Loan!$B$15,2)) |
| Extra payment (G9) | =IF(A9="","",MAX(0,MIN(N(Loan!$B$10)+SUMIFS(Loan!$G$5:$G$28,Loan!$F$5:$F$28,A9),C9-E9))) |
| Ending balance (H9) | =IF(A9="","",ROUND(C9-E9-G9,2)) |
| Cumulative interest (I9) | =IF(A9="","",I8+F9) |
| No-extra balance (L9) | =IF(K9="","",IF(OR(2=Loan!$B$16,L8+K9<=Loan!$B$17),0,ROUND(L8+K9-Loan!$B$17,2))) |
| Payoff date with extra (Loan!C21) | =IF(C20=0,"",INDEX(Schedule!$B$8:$B$487,C20)) |
| Total interest with extra (Loan!C22) | =SUM(Schedule!$F$8:$F$487) |
| Time saved (Loan!B26) | =IF(OR(B21="",C21=""),"",DATEDIF(C21,B21,"y")&" yr "&DATEDIF(C21,B21,"ym")&" mo ("&D20&" fewer payments)") |
Rounding. Interest is rounded to the cent each period, and the payment is rounded to the cent, as on a real statement. That leaves a few cents of drift, so the last payment absorbs it and the balance ends at exactly $0.00. In the example without extra payments, the final payment is $1,580.55 instead of $1,580.17, and total interest is $318,861.58 rather than $1,580.17 × 360 − $250,000 = $318,861.20.
Biweekly and weekly. The rate per payment is the annual rate ÷ 26 or ÷ 52, and dates step by 14 or 7 days. EDATE handles monthly dates, including the 29th to 31st (a 31 Jan first payment falls on 28 or 29 Feb, then 31 Mar). Google's PMT help page makes the same point about units: "Ensure that consistent units are used for rate and number_of_periods."
Extra payments are capped at what is left after the scheduled principal, so the sheet never overpays and the final row can be smaller than the others.
Worked example from the sample data
The Example tabs use a made-up loan: $250,000 at 6.5% a year for 30 years (360 monthly payments), first payment 1 Dec 2026, with $200 extra every month.
1. The scheduled payment
Rate per payment = 6.5% ÷ 12 = 0.5417%. =ROUND(PMT(0.065/12,360,-250000),2) = $1,580.17. That is principal and interest only.
2. The first row
Interest = $250,000.00 × 0.065 ÷ 12 = $1,354.1667, rounded to $1,354.17. Principal = $1,580.17 − $1,354.17 = $226.00. Add the $200.00 extra and the balance falls to $250,000.00 − $226.00 − $200.00 = $249,574.00. Without the extra it would be $249,774.00.
3. The payoff
With $200 a month extra, payment 265 on 1 Dec 2048 is the last: $1,271.37 of balance plus $6.89 of interest = $1,278.26, and no extra is needed. Without extra payments, the loan runs to payment 360 on 1 Nov 2056.
| Example result | Without extra | With $200/month | Difference |
|---|---|---|---|
| Number of payments | 360 | 265 | 95 fewer |
| Payoff date | 1 Nov 2056 | 1 Dec 2048 | 7 yr 11 mo sooner |
| Total interest | $318,861.58 | $221,243.14 | $97,618.44 saved |
| Total extra paid | $0.00 | $52,800.00 | – |
| Total paid (principal + interest) | $568,861.58 | $471,243.14 | $97,618.44 |
The $52,800.00 of extra payments (264 payments × $200; the last payment needs no extra) saves $97,618.44 of interest. Each extra dollar goes straight to principal, so it stops earning interest for the lender for the rest of the loan.
4. Year by year
| Loan year | Principal paid (with extra) | Interest paid (with extra) | Year-end balance, with extra | Year-end balance, no extra |
|---|---|---|---|---|
| 1 | $5,267.12 | $16,094.92 | $244,732.88 | $247,205.69 |
| 2 | $5,619.87 | $15,742.17 | $239,113.01 | $244,224.24 |
| 5 | $6,826.29 | $14,535.75 | $219,892.66 | $234,027.48 |
| 10 | $9,439.52 | $11,922.52 | $178,259.71 | $211,940.39 |
| 15 | $13,053.11 | $8,308.93 | $120,688.95 | $181,397.99 |
| 20 | $18,050.11 | $3,311.93 | $41,079.07 | $139,163.40 |
| 22 | $20,548.76 | $813.28 | $1,271.37 | $118,044.65 |
| 23 | $1,271.37 | $6.89 | $0.00 | $106,413.04 |
| 25 | – | – | $0.00 | $80,760.69 |
| 30 | – | – | $0.00 | $0.00 |
Common mistakes
- Typing the rate as 6.5 instead of 6.5%. The rate cell is a percentage.
B11flags rates above 50%. - Comparing the PMT with your mortgage bill. The bill usually includes escrow for taxes and insurance, and sometimes PMI. Compare the principal-and-interest line instead.
- Using the APR as the rate. The schedule needs the note rate (the interest rate). The APR also reflects certain fees, so it can be higher than the note rate. The APR calculator shows the difference.
- Wrong first payment date. Use the first payment's due date, not the closing or funding date.
- Treating "biweekly" as half the monthly payment. A biweekly schedule here charges interest at the annual rate ÷ 26 with 26 payments a year. A lender's biweekly plan that collects half the monthly payment every two weeks is closer to monthly payments plus one extra payment a year: model it as Monthly with an extra of the payment ÷ 12.
- Expecting the cents to match a statement exactly. Many lenders charge interest daily or by actual days in the month. Small differences are normal; use your statement for payoff amounts.
Limits
- Fixed rate, equal payments, payments at the end of each period. No ARMs, interest-only periods, balloon payments, payment holidays or skipped payments.
- Escrow, property taxes, homeowner's insurance, PMI and fees are not included.
- Interest per period = annual rate ÷ payments per year. Daily-interest loans will differ by a few cents or dollars.
- 480 schedule rows: up to 40 years monthly, about 18 years biweekly, about 9 years weekly.
B11warns when a term needs more rows. - Extra payments always shorten the term; the sheet does not recalculate a lower payment (recasting).
- One currency, no inflation or tax effects.
FAQ
How do I make a loan amortization schedule in Excel?
Work out the rate per payment (annual rate ÷ payments per year) and the number of payments, then get the fixed payment with =PMT(rate, number of payments, -loan amount). For each row: interest = beginning balance × rate per payment, principal = payment − interest, ending balance = beginning balance − principal − any extra payment, and the next row starts from that ending balance. This template has all of that built in for up to 480 payments, with dates, extra payments and a no-extra comparison.
What is the formula for a loan amortization schedule?
The fixed payment is P × r ÷ (1 − (1 + r)^−n), where P is the loan amount, r the rate per payment and n the number of payments; Excel and Google Sheets calculate it with PMT. For the example $250,000 loan at 6.5% for 30 years, r = 0.065 ÷ 12 and n = 360, so the payment is $1,580.17. Each payment's interest is the balance times r, and the rest of the payment reduces the balance.
Why does most of my early payment go to interest?
Interest is charged on the balance, and the balance is highest at the start. The CFPB explains: "In the beginning of your mortgage term, you owe more interest, because your loan balance is still high." In the example, the first payment of $1,580.17 is $1,354.17 interest and $226.00 principal. Without extra payments, principal does not exceed interest until payment 233 (1 Apr 2046).
How much do extra payments save on a loan?
It depends on the rate, the balance and how early the extra money goes in. In the fictional example, $200 a month extra on a $250,000, 6.5%, 30-year loan cuts total interest from $318,861.58 to $221,243.14, saving $97,618.44, and ends the loan 95 payments (7 years 11 months) early. Before paying extra, check your loan has no prepayment penalty and that the lender applies the money to principal.
Does an amortization schedule include taxes and insurance?
No. An amortization schedule covers principal and interest only. The CFPB describes principal, interest, taxes and insurance (PITI) as "the four basic elements of a monthly mortgage payment", and Microsoft notes that the payment returned by PMT "includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans." Add escrow, taxes, insurance and PMI from your loan estimate or statement separately.
Can I use this amortization schedule template in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The formulas use PMT, EDATE, DATEDIF, SUMIFS, INDEX, COUNT, ROUND, MIN, MAX and IF, which work in Excel 2016+, Google Sheets and LibreOffice. There are no macros, no dynamic arrays and no add-ons.
Related tools and templates
- APR calculatorTotal yearly cost of a loan with fees
- Debt payoff spreadsheetSnowball or avalanche for several debts
- Budget spreadsheetFit the payment into your monthly plan
- Bill tracker spreadsheetDue dates for the loan and other bills
- Net worth trackerWatch the loan balance fall over time
- All templatesFree spreadsheets
Educational template only, not financial, tax or legal advice. Example data is fictional: the loan, rate, dates and extra payments are made up to show the formulas and are not a recommendation or a rate quote. Your lender's figures take priority. CFPB, Microsoft and Google material was retrieved on 1 November 2026.