How to build a loan amortization schedule in Excel (with extra payments)

November 4, 202610 min read2,194 words

You take out a 350,000 mortgage and the bank tells you the payment is 2,212.24 a month. It does not tell you that in the first payment, 1,895.83 of it is interest and only 316.40 reduces what you owe. Over 30 years, you will pay back about 796,000 for a 350,000 loan. Most borrowers never see this breakdown until they ask for it, and by then the biggest decisions about extra payments and refinancing are easy to get wrong.

A loan amortization schedule shows every payment split into interest and principal, with the remaining balance after each one. Building one in Excel takes a handful of formulas, and once you have it you can test extra payments, bi-weekly payments, rate changes and refinancing before you commit to any of them. This guide walks through the formulas step by step, with a worked example you can follow cell by cell.

What an amortization schedule shows

Amortization means paying a loan off gradually through regular payments. A schedule lists, for every payment:

The payment number and date
The opening balance
The payment amount
The interest part, charged on the opening balance
The principal part, which reduces the balance
The closing balance

The pattern is the same for every standard loan. Early payments are mostly interest, because the balance is at its largest. As the balance falls, interest shrinks and more of each payment goes to principal. That is why extra payments early in a loan save so much.

Step 1: calculate the payment with PMT

Excel's PMT function returns the regular payment for a fixed-rate loan:

=PMT(rate/12, years*12, -loan)

For a 350,000 loan at 6.5 percent over 30 years:

=PMT(0.065/12, 360, -350000) returns 2,212.24

Divide the annual rate by the number of payments per year, and multiply the years by the same number. The loan amount goes in as a negative so the payment comes out as a positive number.

For other payment frequencies, change both numbers. A bi-weekly loan uses 26 payments a year, so =PMT(rate/26, years*26, -loan). Weekly uses 52.

Step 2: build the schedule row by row

Put the setup values in cells: loan amount, annual rate, number of payments and the payment from Step 1. Then each row of the schedule uses the same simple logic.

Say row 8 is the first payment, with the opening balance in column C.

Interest: =C8 * Rate/12
Principal: =Payment - D8 (where D8 is the interest)
Closing balance: =C8 - E8 (where E8 is the principal)

The next row's opening balance is the previous row's closing balance. Copy the row down for 360 payments and the last balance should reach zero.

For the first payments:

| Payment | Interest | Principal | Balance after | |---|---|---|---| | 1 | 1,895.83 | 316.40 | 349,683.60 | | 2 | 1,894.12 | 318.12 | 349,365.48 | | 3 | 1,892.40 | 319.84 | 349,045.63 | | 12 | 1,876.46 | 335.78 | 346,087.96 |

After a full year of payments totalling 26,547 you have repaid only about 3,912 of the loan. By payment 180, halfway through, the balance is still 253,957, and interest has fallen to 1,380 a month. At the very end, payment 360 is 11.92 of interest and 2,200.32 of principal.

Check your work with built-in functions

Excel has functions that check the schedule without building it:

Interest in a single payment: =IPMT(rate/12, payment_number, 360, -loan)
Principal in a single payment: =PPMT(rate/12, payment_number, 360, -loan)
Total interest over the loan: =-CUMIPMT(rate/12, 360, loan, 1, 360, 0)

The last one returns 446,405.71 for our example, which equals 360 payments of 2,212.24 minus the 350,000 borrowed. If your own schedule's interest column does not add up to the same figure, something is off.

Step 3: add extra payments

Extra payments go straight to principal, so the next month's interest is calculated on a smaller balance. The regular payment does not change, but the loan ends sooner.

Add an Extra column. The closing balance becomes:

=MAX(0, C8 - E8 - Extra)

MAX(0, ...) stops the final balance going negative, and the last payment should be capped at the remaining balance.

Here is what different extra-payment habits do to our 350,000 loan at 6.5 percent. These figures come from running the schedule month by month.

| Plan | Payoff time | Total interest | Interest saved | |---|---|---|---| | Regular payments only | 30 years | 446,406 | none | | 200 extra every month | 23 years 10 months | 338,309 | 108,097 | | 5,000 lump sum every year | 20 years | 277,440 | 168,966 | | Both of the above | 17 years 4 months | 235,470 | 210,936 |

Two things stand out. Extra money early is worth far more than extra money late, and a modest regular amount adds up to a large saving over time. To find the new payoff time without building the whole schedule, use NPER:

=NPER(rate/12, -(payment + extra), loan)

For 200 extra a month this returns about 285.3 payments, which matches the schedule.

Before you pay extra on a real loan, check three things: whether there is a prepayment penalty, whether the extra is applied to principal and not held as an early payment, and whether the money would earn a better return elsewhere, such as paying off higher-interest debt first.

Bi-weekly and weekly payments

Paying half the monthly payment every two weeks gives 26 half payments a year, which equals 13 full monthly payments instead of 12. That extra payment a year shortens the loan.

On our example, paying 1,106.12 every two weeks pays the loan off in about 24 years and 2 months, and total interest falls from 446,406 to about 343,597, a saving of around 102,800. This is an estimate: many lenders apply a payment only when they receive it, some do not accept bi-weekly plans, and some charge a fee. Ask your lender whether payments are applied immediately and whether the extra goes to principal.

You can get most of the same effect by dividing the monthly payment by 12 and adding that amount to each monthly payment, which is simpler and works with any lender.

Fixed payment vs fixed principal

Most mortgages use a fixed payment. A fixed-principal loan repays the same principal each time, so payments start high and fall as interest shrinks.

For our loan, the principal portion would be 350,000 / 360 = 972.22 every month. The first payment is 972.22 plus 1,895.83 of interest, or 2,868.06, and the last is about 977.49. Total interest is about 342,198, much lower than the 446,406 of a fixed payment, but the first payments are about 30 percent higher. Which one is better depends on your cash flow.

Variable and adjustable rates

With an adjustable-rate loan the schedule has to change rate partway through. The usual approach is to list each date and new rate, and recalculate from that point.

Suppose the rate rises to 7.5 percent after five years. The balance at that point is 327,638 with 300 payments left. There are two ways lenders handle the change:

Recalculate the payment over the remaining term. The new payment is =PMT(0.075/12, 300, -327638.42), which is 2,421.22, about 209 more a month.
Keep the payment and run the loan longer. At 2,212.24 the balance now takes about 417 payments, not 300, to clear.

Run both to see the difference before you rely on a low introductory rate.

When does refinancing pay off?

Refinancing replaces your loan with a new one. It helps when the new payment plus costs beats keeping the old loan, and the break-even point tells you how long it takes.

Example: after 5 years you owe 327,638. You refinance into a 25-year loan at 5.5 percent with closing costs of 4,500.

New payment: =PMT(0.055/12, 300, -327638) is 2,011.99
Saving: 2,212.24 minus 2,011.99 = 200.25 a month
Break-even: 4,500 / 200.25 = 22.5 months

If you expect to keep the loan beyond about two years, the saving covers the cost. Also compare total interest. A longer new term can lower the payment and still increase what you pay overall, and rolling closing costs into the loan raises the balance you pay interest on.

APR: the number that includes fees

The interest rate does not include fees. APR (annual percentage rate) spreads fees across the loan, so it is the better way to compare offers. In Excel, use RATE with the loan amount reduced by the upfront fees:

=RATE(360, -2212.24, 350000 - 5250) * 12

With 5,250 in fees (1.5 percent of the loan), the APR is about 6.65 percent, against a 6.5 percent rate. Compare offers on APR and total cost, and read the lender's own disclosure, because official APR rules differ by country.

Mistakes that break amortization schedules

Using the annual rate without dividing. A monthly schedule needs rate / 12.
Mismatching the rate and the number of payments. A bi-weekly loan needs rate / 26 and 26 payments a year.
Letting the balance go negative on the last payment. Cap it with MIN or MAX.
Rounding too early. Round for display, not in the calculation, or the last balance will not reach zero.
Ignoring fees, insurance and escrow. The schedule covers the loan only. Property tax and insurance sit on top.
Treating extra payments as pre-paying the next one. Make sure they reduce principal.
Trusting the schedule over your lender's statement. Lenders differ on day counts, rounding and fees.

A ready-made amortization workbook

The formulas above build a simple schedule in minutes. Adding extra payments in several forms, rate changes, interest-only periods, balloons and a refinance comparison without a broken reference takes much longer. The Loan Amortization Schedule is an Excel workbook that does it all.

Enter the amount, rate, term, payment frequency and first payment date once, and it lists every payment for up to 1,560 payments, from monthly to annual. Choose a fixed payment or fixed principal, add interest-only payments or a balloon, and list rate changes by date, with the payment either recalculated or kept so the term changes. Extra payments can be added with each payment, every few payments, between two payment numbers, as an annual lump sum or as up to 12 one-off payments by date, and the workbook runs the loan with and without the extras side by side.

The Refinance tab compares the rest of your current loan with a new one, including closing costs and points, and shows the break-even point and a plain-language verdict. Compare Offers puts three loan quotes side by side on payment, total interest, fees, points, APR and total borrowing cost. The Dashboard shows interest saved, the debt-free date and balance charts, and the Annual Summary gives the schedule by year. Figures are estimates. It locks the formulas without a password and contains no macros.

If you want something narrower, these templates cover one part of the job:

Common questions

How do you calculate a loan payment in Excel?

Use PMT(rate / payments per year, number of payments, -loan amount). For 350,000 at 6.5 percent over 30 years with monthly payments, the result is 2,212.24. Each payment's interest is the opening balance times the rate divided by payments per year, and the rest goes to principal.

How do extra payments change an amortization schedule?

They go straight to principal, so less interest builds up and the loan ends sooner while the regular payment stays the same. In the example above, 200 extra a month saves about 108,000 in interest and cuts roughly 6 years from the loan.

How do I calculate total interest on a loan in Excel?

Use =-CUMIPMT(rate/12, number_of_payments, loan, 1, number_of_payments, 0), or multiply the payment by the number of payments and subtract the loan amount.

Can an amortization schedule handle bi-weekly payments?

Yes, as long as the rate and number of periods use 26 payments a year. Check with your lender how bi-weekly payments are applied, because policies differ.

How do I know if refinancing is worth it?

Add closing costs and points, work out the new payment and divide the costs by the monthly saving to get a break-even point. Also compare total interest over the life of the loan, since a longer term can cost more even with a lower payment.

Will the schedule match my lender's statement?

It will be close, but lenders can differ on day counts, rounding and fees, so treat the figures as estimates. Your lender's statement is final.

Does it work in Google Sheets?

The workbook is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over, so the locked formulas are easier to overwrite there by accident.

Seeing every payment laid out turns a loan from a monthly bill into a plan you can improve. You can preview the Loan Amortization Schedule free; downloading it is included in the OpenWorksheet monthly plan, along with the rest of the library. All figures are examples and estimates, not financial advice, so check the terms of your own loan with your lender.