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.

Download Excel template (.xlsx) 178 KB · no macros · Excel 2016+ and Google Sheets · v1.0, 1 Nov 2026

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.

Preview: the Example Loan and Example Schedule tabs. Fictional loan: $250,000 at 6.5% for 30 years, monthly payments from 1 Dec 2026, $200 extra every month.
$1,580.17scheduled payment (PMT, principal + interest)
1 Dec 2048payoff with extra (265 payments) vs 1 Nov 2056 (360)
$97,618.44interest saved: $221,243.14 vs $318,861.58
7 yr 11 motime saved (95 fewer payments)
No.Payment dateBeginning balancePaymentPrincipalInterestExtraEnding balanceCumulative interest
11 Dec 2026$250,000.00$1,580.17$226.00$1,354.17$200.00$249,574.00$1,354.17
21 Jan 2027$249,574.00$1,580.17$228.31$1,351.86$200.00$249,145.69$2,706.03
31 Feb 2027$249,145.69$1,580.17$230.63$1,349.54$200.00$248,715.06$4,055.57
41 Mar 2027$248,715.06$1,580.17$232.96$1,347.21$200.00$248,282.10$5,402.78
51 Apr 2027$248,282.10$1,580.17$235.31$1,344.86$200.00$247,846.79$6,747.64
61 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 …
2631 Oct 2048$4,789.33$1,580.17$1,554.23$25.94$200.00$3,035.10$221,219.81
2641 Nov 2048$3,035.10$1,580.17$1,563.73$16.44$200.00$1,271.37$221,236.25
2651 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.
Example loan balance at the end of each year: $250,000 at 6.5% for 30 years. With $200 a month extra the balance reaches $0 on 1 Dec 2048 after 265 payments; without extra payments it reaches $0 on 1 Nov 2056 after 360 payments. Balance after 10 years: $178,259.71 with extra, $211,940.39 without.$0k$50k$100k$150k$200k$250k051015202530Years from first payment$200 extra a month: paid off 1 Dec 2048No extra: paid off 1 Nov 2056

Chart: year-end balance from the Example Loan tab's year-by-year table. The workbook draws it as a native line chart, plus a stacked column chart of principal vs interest paid each year.

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, how the maths works, colour key, limits, disclaimer
LoanLoan 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 numberInput 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
ScheduleNothingUp 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 ScheduleAlready filledThe 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

  1. 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.
  2. 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).
  3. Check that B11 says OK. If not, it tells you which input to fix.
  4. 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 at F5:G28; column H shows the date of that payment.
  5. Read the Summary: scheduled payment, payoff date with and without extra, number of payments, total interest, interest saved and time saved.
  6. 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.

Example loan, no extra payments: principal (purple) and interest (amber) paid in each year. The yearly total is the same; the split changes.
Principal and interest paid in each year of the example loan with no extra payments. Year 1: $2,794.31 principal, $16,167.73 interest. Year 15: $6,924.94 principal, $12,037.10 interest. Year 30: $18,311.31 principal, $651.11 interest.$0k$5k$10k$15k$20k$25k151015202530Loan year (no extra payments)PrincipalInterest

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:

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.

FieldFormula
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 resultWithout extraWith $200/monthDifference
Number of payments36026595 fewer
Payoff date1 Nov 20561 Dec 20487 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 yearPrincipal paid (with extra)Interest paid (with extra)Year-end balance, with extraYear-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

Limits

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

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.