Loan amortization schedule in Excel

A loan amortization schedule shows every payment on a loan and splits it into interest and principal. In a spreadsheet you build it from four inputs and a handful of formulas. This page walks through one small business loan, row by row, and explains why each formula is written the way it is.

What a loan amortization schedule is

To amortize a loan means to pay it off in regular installments. Most business loans and car loans use a fixed payment: the same amount every month until the balance reaches zero. The payment never changes, but what it pays for does.

Each payment has two parts:

• Interest is the lender's charge for the month. It equals the balance still owed times the monthly interest rate.
• Principal is the rest of the payment. It reduces the balance.

Early in the loan the balance is large, so interest takes a big share of each payment. As the balance falls, interest shrinks and more of the same payment goes to principal. The schedule makes that shift visible, one row per payment.

Accountants use the schedule for real work. The interest column is the interest expense to record each month. The ending balance is the loan liability on the balance sheet. And the split between principal due in the next twelve months and the rest tells you how much of the loan is a current liability. For the entry that records the monthly interest, see accrued expenses adjusting entries.

Why this matters for MO-220 practice

The Excel for Accounting certification exam (MO-220) lists "Perform routine accounting activities" as one of its skill areas. Under it, amortization schedules cover two tasks: calculating fixed monthly payments and creating the schedule itself. This lesson practices both.

One thing to know about the exam: its task instructions describe the purpose, not the function. You will read something like "calculate the monthly payment in B8", not "use PMT". So you need to know which function fits which job, and how its arguments work. That is the habit this page and its exercises build. Accounting Exercises is not affiliated with Microsoft or Certiport.

Worked example: Maple Lane Catering

Maple Lane Catering borrowed to pay for a storefront upgrade. The loan is repaid in equal monthly payments. The sheet holds the loan terms in column B:

The task: calculate the monthly payment in B8, show it as a positive amount, and use the cells above rather than typing the numbers.

Step 1: put the rate and the term in months

The payment is monthly, so the rate and the number of payments must be monthly too. That is the single most important rule in loan math.

• Monthly rate: B4/B6, which is 5.75% / 12 = 0.4792% a month.
• Number of payments: B5*B6, which is 6 × 12 = 72 payments.

Referring to B6 instead of typing 12 keeps the sheet honest. If the loan were paid quarterly, you would change one cell and every formula would follow.

Step 2: calculate the payment with PMT

The PMT function returns the fixed payment for a loan. It takes the rate per period, the number of periods and the present value (the amount borrowed). In B8:

=PMT(B4/B6,B5*B6,-B3)

The result is $172.78 a month.

Why the minus sign in front of B3? Spreadsheet financial functions follow a cash-flow sign rule: money you receive and money you pay out have opposite signs. The business receives $10,500 and pays back $172.78 a month. If you enter the loan as a positive number, PMT returns the payment as a negative one. Making the loan amount negative inside the function gives a positive payment, which is how a schedule normally shows it.

Step 3: lay out the schedule

A schedule has one row per payment and five working columns. In the lesson's second exercise the header sits in row 10 and the first payment in row 11:

Notice the dollar signs. The payment, the rate and the payments per year live in single cells that every row must use, so they are locked with absolute references ($B$8, $B$4, $B$6). The balance references stay relative, so each row points at its own beginning balance and the row above. Write row 12 once, then fill it down to the last payment.

Step 4: read the first rows

Here is how Maple Lane's schedule starts, using the same formulas:

Month 1 interest is $10,500.00 × 5.75% / 12 = $50.31. The rest of the payment, $122.47, reduces the balance to $10,377.53. Month 2 charges interest on that lower balance, so interest drops to $49.73 and principal rises to $123.05. By the last payment, almost all of it is principal, and the ending balance lands on zero.

Step 5: total interest, and one payment on its own

Over the life of the loan Maple Lane pays 72 × $172.78 = $12,440.08 in total. Subtract the $10,500 borrowed and the total interest is $1,940.08. In a sheet that is =B8*B5*B6-B3.

Sometimes you only need one row. The IPMT and PPMT functions return the interest part and the principal part of a chosen payment, with the same arguments as PMT plus the payment number. The lesson's third exercise uses them for Harbor Bakery's $11,500 loan at 6.75% over 3 years. The payment is $353.77, and payment 18 splits into $35.76 of interest (=IPMT(B4/B6,B10,B5*B6,-B3)) and $318.01 of principal (=PPMT(B4/B6,B10,B5*B6,-B3)). The two parts add back to the payment, which is a quick check that both are right.

Common mistakes

These are the mistakes the exercises are built to catch. Each one produces a number that looks plausible, which is why it is worth knowing them in advance.

Using the annual rate as the monthly rate

Writing =PMT(B4,B5*B6,-B3) charges 5.75% every month instead of every year. For Maple Lane the payment jumps from $172.78 to $614.73. The fix is to divide the annual rate by the payments per year. All three exercises check for this, because it is the most common loan error by far. If your payment looks several times too large, look here first.

Showing the payment as a negative number

=PMT(B4/B6,B5*B6,B3) returns −$172.78. The size is right but the sign is not. A schedule built on a negative payment will add to the balance instead of reducing it. Put the minus sign on the loan amount inside the function.

Charging interest on the original amount every month

In the twelve-month schedule exercise, Pine Street Dental borrows $54,000 at 3.75%. Month 1 interest is $168.75. A common slip is to write the interest formula as =$B$3*$B$4/$B$6, which charges $168.75 every month. Interest must use each row's beginning balance. With the slip, total interest comes to $2,025.00 instead of $1,103.15, and the final balance is $921.85 instead of zero. A final balance that is not zero is the clearest sign something is off.

Swapping interest and principal

When you calculate one payment's parts, it is easy to put PPMT where IPMT belongs. For Harbor Bakery's payment 18, the swap shows $318.01 as interest and $35.76 as principal. Remember the pattern: in the middle of a three-year loan, interest is the smaller part.

How to check your schedule

• The final ending balance should be zero (or a tiny rounding difference such as 0.00).
• Interest plus principal should equal the payment on every row.
• The principal column should add up to the original loan amount.
• The interest column should add up to total payments minus the loan amount.
• Interest should fall and principal should rise from one row to the next.

Related topics: depreciation schedules in Excel use the same row-by-row pattern for fixed assets, and trial balances in Excel show where the loan balance ends up.