Build a Loan Amortization Schedule in Excel

A loan amortization schedule in Excel is a table with one row per payment. It shows how much of each payment goes to interest, how much pays down the loan, and the balance that's left. You need one PMT formula for the payment and five short formulas that you fill down. This guide builds a 12-month schedule for a 10,000 loan, step by step, and checks that it ends at exactly zero.

What an amortization schedule shows#

Amortization means paying off a loan in regular, equal payments. Each payment covers that month's interest first, and the rest reduces the balance. Early on, more of each payment goes to interest. Near the end, almost all of it pays off the loan.

The schedule has these columns:

Step 1: Enter the loan inputs#

Put the inputs at the top in their own cells, so you can change the loan later without touching the formulas.

Type the rate as 6% or 0.06. Both mean the same thing to Excel.

Step 2: Work out the monthly payment with PMT#

PMT returns the fixed payment for a loan with a constant rate. In B4, type:

=ROUND(PMT(B2/12,B3,-B1),2)

The result is 860.66. Here is what each part does:

• B2/12 turns the yearly rate into a monthly rate: 0.5%.
• B3 is the number of payments: 12.
• -B1 is the loan amount. The minus sign makes the payment positive, because PMT treats money paid out as negative.
• ROUND(..., 2) rounds the payment to cents, as a lender would.

The guide to the PMT function in Excel explains PMT's optional arguments, such as a balloon payment at the end.

Step 3: Build the first row#

Put the headers in row 6: Month, Opening balance, Payment, Interest, Principal, Closing balance. Then fill row 7:

The payment formula in C7 uses the fixed payment in every month except the last. In the last month it pays exactly what is left, which Step 5 explains.

Interest in D7 is the opening balance times the monthly rate: 10,000 × 0.5% = 50.00. ROUND keeps it to cents so the totals add up the way a bank statement would. The ROUND function guide covers the second argument, the number of decimal places.

Step 4: Fill the schedule down with absolute references#

Row 8 links to row 7, and then everything fills down. In A8, type =A7+1. In B8, type =F7, so each month opens with the previous month's closing balance. Copy C7:F7 down to row 8.

Now select A8:F8 and fill down to row 18 with Ctrl+D (Mac: ⌘D). The $ signs keep $B$2, $B$3 and $B$4 fixed on the inputs, while references like B7 and D7 move down one row at a time. Without the $, the rate reference would slide down to B3, B4 and beyond, and the interest would be wrong from row 8. Press F4 (Mac: ⌘T) to add the signs as you type. Practise relative and absolute references if this is new.

Here are the first three months and the last two:

Watch the interest shrink each month while the principal grows. That's amortization at work.

Step 5: Make the schedule end at exactly zero#

Rounding to cents leaves a small difference at the end. With the fixed 860.66 in month 12, the closing balance would be 0.04, not zero. That's why C7 uses IF: in the final month, the payment becomes the opening balance plus interest, 856.42 + 4.28 = 860.70, and the closing balance is 0.00.

Always check the last closing balance. If it's far from zero, a reference is wrong, usually a missing $. Rounding choices like this are covered in the ROUND, ROUNDUP and ROUNDDOWN lesson.

Step 6: Add totals and checks#

Two totals tell the story of the loan. Below the schedule, add:

=SUM(D7:D18)

Total interest is 327.96. And total paid:

=SUM(C7:C18)

That's 10,327.96: the 10,000 borrowed plus the interest. A simple check is that =SUM(E7:E18) equals the loan amount, 10,000.00.

To see the effect of a different loan, change the inputs. Try 24 months or 8% and the whole schedule updates, as long as you extend the rows to match the number of months. The guide to compound interest in Excel uses the same monthly rate idea for savings.

Variations: extra payments and longer loans#

A few changes make the schedule fit more situations:

• Extra payments. Add a column for extra payments and change the closing balance to =B7-E7-G7. The balance drops faster and the last rows show zero early. Wrap the payment in MIN so you never pay more than you owe.
• Long loans. A 25-year mortgage has 300 rows. Fill the formulas down to row 306 and freeze the header row with View > Freeze Panes.
• Unrounded schedules. Some lenders don't round each month. Remove ROUND from the interest column to match them. The IF in the payment column still clears the last balance.

Schedules like this are a classic task in finance interviews. The Excel skills for finance jobs article lists the related skills, and the finance Excel test lets you try them against the clock.

Practise it#

Start with the exercise on this page: one PMT formula wrapped in ROUND. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet and practise absolute references, the two skills that make every row of the schedule come out right.