How to Make a Loan Amortization Schedule in Excel (Step-by-Step Formulas)

An amortization schedule shows every payment on a loan, how much of it goes to interest, how much reduces the principal, and what balance is left afterward. Excel can build one in about ten minutes with one built-in function and three simple row formulas.

This guide walks through a complete example you can copy cell by cell, shows the output you should get, adds an optional extra-payment column, and explains how to check your spreadsheet against our free loan amortization calculator.

Quick Answer: How Do You Make an Amortization Schedule in Excel?

1. Enter the loan amount, annual interest rate, term in years, and payments per year in separate cells.
2. Calculate the payment with =PMT(rate/12, years*12, -loan_amount).
3. In each row of the schedule, calculate interest as beginning balance × (annual rate ÷ 12).
4. Principal = payment − interest, and ending balance = beginning balance − principal.
5. Use each ending balance as the next row's beginning balance, and copy the row down for every payment.

The rest of this article turns those steps into exact formulas.

The Example Loan Used in This Guide

Every formula below uses the same loan so you can check your work at each step:

• Loan amount: $20,000
• Annual interest rate: 7.5% (fixed)
• Term: 5 years (60 monthly payments)
• Assumptions: monthly payments, interest calculated monthly on the remaining balance, no fees

With these inputs, the monthly payment should come out to $400.76 and total interest to $4,045.54.

Step 1: Set Up the Input Cells

Open a blank sheet and enter the labels in column A and the values in column B:

Type the rate with the percent sign (7.5%) so Excel stores it as 0.075. Keeping every input in its own cell means you can change the loan later without touching a single formula.

Step 2: Calculate the Monthly Payment With PMT

In B6, enter:

=PMT(B2/B4, B3*B4, -B1)

PMT takes three required arguments:

• rate – the interest rate per period. B2/B4 converts the 7.5% annual rate into a monthly rate.
• nper – the total number of payments. B3*B4 gives 5 × 12 = 60.
• pv – the loan amount. The minus sign is deliberate: Excel treats money you receive as positive and payments as negative, so without it PMT returns −$400.76.

B6 should now show $400.76.

Step 3: Add the Schedule Headers

Leave a couple of blank rows, then enter these headers in row 9:

Step 4: Build the First Row

Row 10 is payment 1. Enter these formulas:

The dollar signs in $B$2, $B$4, $B$5 and $B$6 are absolute references. They keep those cells locked when you copy the formulas down. Forgetting them is the most common reason a schedule breaks after row 10.

Row 10 should show: beginning balance $20,000.00, payment $400.76, interest $125.00, principal $275.76, ending balance $19,724.24.

How each row works: interest comes from the beginning balance, principal is what's left of the payment, and the ending balance feeds the next row.

Step 5: Link the Second Row and Copy It Down

Row 11 is identical to row 10 except for two cells:

• A11: =A10+1
• B11: =G10 (each beginning balance is the previous ending balance)

Copy C10:G10 into C11:G11. Then select A11:G11 and drag the fill handle down to row 69, which gives 60 rows for 60 payments. The ending balance in G69 should be $0.00.

The finished layout: inputs in B1:B6, headers in row 9, and the schedule from row 10 down.

Step 6: Add Totals and Check the Result

Add two summary formulas beside the inputs:

• Total interest: =SUM(D10:D69) → $4,045.54
• Months to payoff: =COUNTIF(C10:C69, ">0") → 60

Compare a few rows with the reference values below. If they match, your schedule is correct.

Selected rows. Excel keeps full precision and displays values rounded to the cent.

Notice how the split changes over time. In month 1, $125.00 of the $400.76 payment goes to interest. By month 60, only $2.49 does. Interest is always calculated on the remaining balance, so it shrinks as the balance falls.

Each payment is the same $400.76, but the interest share shrinks every month.

How to Add Extra Payments to Your Excel Schedule

The schedule already includes an extra payment column, so you only need to change one input. Enter 100 in B5 to add $100 to every monthly payment, and the schedule updates instantly:

Two formulas make this work. MIN($B$5, B10-E10) stops the extra payment from exceeding the balance, and MIN($B$6, B10+D10) shrinks the final payment once the loan is almost paid off. After payoff, the remaining rows show zeros instead of negative balances.

Adding $100 a month pays the loan off 13 months early and saves $964.55 in interest.

This model assumes your lender applies extra money directly to principal and keeps the scheduled payment the same, so the loan ends sooner. Some lenders handle prepayments differently or charge prepayment fees, so check your loan agreement before you rely on the savings figure.

Shortcut: IPMT, PPMT and CUMIPMT

If you only need the interest or principal for one specific payment, Excel has dedicated functions:

Change the second argument of IPMT or PPMT to any payment number from 1 to 60. CUMIPMT requires a positive loan amount and returns a negative number, which is why the formula starts with a minus sign.

These functions assume the standard schedule with no extra payments. Once you add extra payments, use the row-by-row method above.

Does This Work in Google Sheets?

Yes. Google Sheets supports PMT, IPMT, PPMT, CUMIPMT, MIN, SUM and COUNTIF with the same syntax. You can enter every formula in this guide exactly as written. The only thing to check is that the interest rate cell is formatted as a percentage.

Common Excel Amortization Errors and How to Fix Them

The payment is far too high

You probably used the annual rate as the periodic rate. The rate argument must be divided by the number of payments per year: B2/B4, not B2.

PMT returns a negative number

This is how Excel signs cash flows. Put a minus sign in front of the loan amount (-B1) or in front of PMT itself.

The numbers go wrong after the first row

Check for missing dollar signs. If $B$6 was entered as B6, Excel moves the reference down a row each time you copy, and the payment cell becomes blank.

The final balance is a few cents away from zero

This can happen if you rounded the payment manually, for example by typing 400.76 instead of using the PMT result. Keep the payment as a formula, or wrap the interest in ROUND(...,2) if you want to match a lender that rounds every month. Small differences of a few cents are normal and come from rounding.

The schedule runs past the end of the loan

If you copied the rows further than needed, the MIN formulas keep the extra rows at zero. You can delete them or leave them.

Check Your Spreadsheet With a Loan Amortization Calculator

A spreadsheet is easy to break with one wrong reference, so it pays to compare it with an independent result. Open the free loan amortization calculator, enter the same loan amount, rate and term, and compare the payment and a few balances with your sheet. If you use the example loan, the payment should be $400.76.

The calculator also shows the complete schedule without any setup, which is the quicker option when you only need the numbers and don't need to customize the spreadsheet.

Related tools: to compare different loan scenarios, try the Advance Loan Calculator. For a home loan, use the Mortgage Calculator.