How to Build an Amortization Schedule in Excel

An amortization schedule breaks down every loan payment into its two components — principal and interest — across the full life of a loan. Building one in Excel gives you a live, customizable tool that shows exactly where your money goes each month, how extra payments affect your payoff timeline, and what your remaining balance looks like at any point. Here's how it works and what you need to know to build one from scratch.

What an Amortization Schedule Actually Shows

Each loan payment you make serves two purposes: part of it reduces the outstanding principal (the amount you borrowed), and part covers the interest charged on that remaining balance. Early in a loan, the split heavily favors interest. As the balance shrinks, more of each payment goes toward principal.

An amortization schedule maps this shift, row by row, for every payment period — typically monthly. The key columns in any complete schedule are:

Setting Up Your Input Variables

Before building the schedule itself, create a clean input section at the top of your spreadsheet. This keeps your key variables in named cells that your formulas can reference, making the entire sheet easy to update.

The four inputs you need:

• Loan amount — the total amount borrowed
• Annual interest rate — the stated rate on the loan
• Loan term — total number of years (or months)
• Start date — optional, but useful for tracking real payment dates

Place these in cells like B1 through B4, then label them clearly in column A.

Calculating the Fixed Monthly Payment with PMT

Excel's built-in PMT function handles the monthly payment calculation automatically. The syntax is:

=PMT(rate, nper, pv) 

• rate = monthly interest rate (annual rate ÷ 12)
• nper = total number of payments (years × 12)
• pv = present value, or loan amount (entered as a negative to return a positive payment)

Example formula, assuming your annual rate is in B2, term in years in B3, and loan amount in B1:

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

This returns a fixed monthly payment figure. Lock those input cell references with $ signs (e.g., $B$2) so they don't shift when you copy formulas down.

Building the Schedule Row by Row 📋

Set up your column headers in row 6 or 7, then start your first data row below. Here's the logic for each formula:

Opening Balance (first row): Reference your loan amount input directly.

=B1 

Interest Paid:

=OpeningBalance * ($B$2/12) 

Principal Paid:

=MonthlyPayment - InterestPaid 

Closing Balance:

=OpeningBalance - PrincipalPaid 

Opening Balance (all subsequent rows):

=Previous row's Closing Balance 

Once you have the second row's formulas working correctly, select all cells in that row and drag them down for the full number of payment periods. For a 30-year mortgage, that's 360 rows. For a 5-year car loan, it's 60.

Preventing Rounding Errors at the End

Due to how floating-point arithmetic works in Excel, your final payment may be a few cents off, leaving a small residual balance instead of an exact zero. Two common fixes:

• Wrap your Closing Balance formula in a rounding function: =ROUND(OpeningBalance - PrincipalPaid, 2)
• Use an IF statement on the final row to cap the closing balance at zero if it drops below a threshold

Neither approach is universally "correct" — the right method depends on whether you need the schedule for internal tracking or for matching an actual lender's statement.

Adding Extra Payment Scenarios 🔢

One of the most useful features you can add is an extra payment column. Insert a column for additional monthly payments, then modify your Principal Paid formula to include it:

=MonthlyPayment + ExtraPayment - InterestPaid 

Then wrap your Closing Balance formula so it can't go below zero:

=MAX(0, OpeningBalance - PrincipalPaid) 

Finally, add a condition to stop calculating once the balance reaches zero — either by using IF statements or by filtering out rows where the opening balance is already at zero. This lets you model what happens if you pay an extra $100 or $500 per month, and see exactly how many months it shaves off.

What Changes Based on Your Loan Type

Not all loans follow the same structure, and this is where setup decisions start to diverge meaningfully:

• Fixed-rate loans are the simplest — one PMT calculation, then a straight row-fill.
• Adjustable-rate loans require the interest rate to change at defined intervals, which means separate rate inputs per period and more complex formula logic.
• Interest-only periods require splitting the schedule into two phases with different payment formulas.
• Bi-weekly payment schedules use a different payment frequency, changing both the PMT calculation and how interest accrues.

The complexity of your Excel schedule scales directly with the complexity of your loan terms. A basic fixed-rate schedule can be built in under 30 minutes; modeling a hybrid ARM with extra payments and a balloon payment requires considerably more formula work.

The Variables That Shape Your Specific Build

Even for a straightforward loan, a few things determine what the right setup looks like for your situation: whether you want the schedule to auto-stop at payoff or run fixed rows to term, whether you're reconciling against a lender's numbers or just projecting, and how comfortable you are using named ranges versus direct cell references. The Excel version you're running can also matter — some functions behave slightly differently across versions or between Excel and Google Sheets.

The mechanics are consistent, but the right level of complexity and the specific formulas you'll lean on depend entirely on your loan structure and what you actually need the schedule to do.