How to Build an Excel Amortization Schedule (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

An excel amortization schedule is a table that splits every loan payment into interest and principal, then tracks the remaining balance period by period. You can build one with four inputs and three functions: PMT for the payment, IPMT for the interest portion, and PPMT for the principal portion. The whole build takes about ten minutes, and the schedule updates automatically whenever you change a loan term.

Quick Answer

Put the loan amount, annual rate, term in years, and payments per year in four labeled input cells.
Compute the periodic rate as annual rate divided by payments per year, and the total periods as years times payments per year.
Use =PMT(rate, nper, -pv) to get the fixed payment, then =IPMT(...) and =PPMT(...) for each period's split.
Add a balance column that subtracts principal from the previous balance, and check that the final balance lands at or near zero.
Lock the input cells with absolute references ($B$1) before you drag the formulas down.

Before You Start

You need four numbers: the loan amount (present value), the annual interest rate, the loan term, and how often you pay. Everything else in the schedule is derived from those.

Set up a small input block at the top of the sheet so the schedule formulas can point at fixed cells. A layout like this works well:

Cell	Label	Example value
B1	Loan amount	200000
B2	Annual interest rate	0.06
B3	Term in years	30
B4	Payments per year	12

Enter the rate as a decimal (0.06) or as a percentage (6%) and format the cell accordingly. Excel treats both the same way in calculations.

Two derived values drive the whole schedule. The periodic rate is the annual rate divided by the number of payments per year. The total number of periods is the term in years multiplied by the payments per year. For a monthly loan, that is 12 periods per year.

One convention matters here. Excel's financial functions expect the rate to match the period. If you pay monthly, use a monthly rate, not an annual one. Mixing the two is the single most common reason a schedule looks wrong.

If you are new to writing formulas, review how to make a formula in Excel before you start. The syntax rules for functions and cell references apply to everything below.

Step by Step

Enter the inputs. Fill B1 through B4 with the loan amount, annual rate, term in years, and payments per year. Label each one in column A so the sheet explains itself.

Compute the periodic rate. In a cell below the inputs, enter =B2/B4. This converts the annual rate into the rate per payment period.

Compute the total periods. In the next cell, enter =B3*B4. For a 30-year monthly loan this returns 360.

Calculate the payment. Use the PMT function: $$PMT = \frac{r \cdot PV}{1 - (1+r)^{-n}}$$ In Excel, enter =PMT(rate, nper, -pv) where rate is the periodic rate cell, nper is the total periods cell, and pv is the loan amount. The negative sign on pv makes the payment come out positive. If you leave it off, the payment shows as a negative number.

Build the period column. In the first data row, enter 1. In the row below, enter =A8+1 (adjust to your actual cell) and drag down until you reach the total number of periods.

Add the payment column. In the first data row, enter =$B$5 where B5 holds your PMT result, or repeat the PMT formula with absolute references. Lock every input reference with dollar signs so the formula survives being dragged down.

Add the interest column. Use IPMT, which returns the interest portion of a payment for a given period: =IPMT($B$6, A8, $B$7, -$B$1) Here B6 is the periodic rate, A8 is the current period number, B7 is total periods, and B1 is the loan amount. The period argument changes as you drag down, which is exactly what you want.

Add the principal column. Use PPMT the same way: =PPMT($B$6, A8, $B$7, -$B$1) Interest plus principal should equal the payment in every row. If it does not, one of your references is not locked.

Add the balance column. The first row's balance is the loan amount minus that period's principal. Every row after that is the previous balance minus the current principal: =E8-D9 in row 9, where E8 is the prior row's balance and D9 is this period's principal (with period in A, payment in B, interest in C, principal in D and balance in E).

Format and check. Format the money columns as currency and the rate as a percentage. Scroll to the last row. The ending balance should be zero or within a cent or two of zero.

A quick note on dates: if you want a date column, start with the first payment date and add one month per row. The article on adding days to a date in Excel covers the date arithmetic if you need it.

Worked Example

Imagine a loan of 100,000 at 12% per year, paid monthly for 10 months. The periodic rate is 12% divided by 12, which is 1% per month. The total periods are 10.

The payment comes from PMT with a 1% rate, 10 periods, and a present value of 100,000. In period 1, the interest portion is 1% of 100,000, which is 1,000. The principal portion is the payment minus 1,000. The ending balance is 100,000 minus that principal.

In period 2, the interest is 1% of the new, smaller balance, so it is slightly less than 1,000. The principal portion grows by the same amount. This pattern repeats every period: interest shrinks, principal grows, and the payment stays fixed. By period 10 the balance reaches zero.

That is the entire logic of an amortization schedule. The numbers change with the loan, but the structure never does.

Other Ways to Do It

Use a single formula per column. Instead of typing IPMT and PPMT into every row, enter them once in the first data row and drag the fill handle down. Excel adjusts the period argument automatically while the locked references stay put.

Use CUMIPMT and CUMPRINC for totals. These functions return cumulative interest or principal between two periods. They are useful for a summary row that shows total interest paid over the life of the loan.

Use a template. Excel ships with loan amortization templates you can find through the New workbook screen. They work, but building your own teaches you what each column means, which matters when a number looks off.

Use a data table for scenarios. If you want to compare several interest rates side by side, Excel's What-If Analysis tools can generate a table of payments across a range of rates without rebuilding the schedule.

Troubleshooting

The payment is negative. You left the minus sign off the present value argument. Add it: -pv.

The final balance is not zero. Check that your period column runs exactly to the total periods and that the balance formula references the row above it, not the row above that.

Interest and principal do not sum to the payment. One of your references is relative when it should be absolute. Lock the rate, nper, and pv cells with dollar signs.

The numbers are wildly off. You are probably using an annual rate where a periodic rate belongs. Divide the annual rate by the payments per year.

Dragging the formula breaks it. This is the same absolute reference problem. Select the reference in the formula bar and press F4 to cycle through the reference types.

Common Mistakes

Using the annual rate in PMT. The rate argument must match the payment period. Divide by 12 for monthly payments, 4 for quarterly, 52 for weekly. Fix: compute a separate periodic rate cell and reference it everywhere.
Forgetting the negative sign on pv. Without it, the payment returns negative and every downstream column inherits the sign error. Fix: enter -pv in the formula.
Relative references that shift on drag. If B1 becomes B2 as you drag down, the schedule silently reads the wrong input. Fix: press F4 to make references absolute before dragging.
Starting the period column at 0. IPMT and PPMT expect period 1 for the first payment. A zero start throws off every row. Fix: begin at 1.
Rounding the payment but not the split. If you round the payment to cents but leave interest and principal unrounded, the columns stop adding up. Fix: round consistently, or leave everything unrounded and format for display only.
Ignoring extra payments. A standard schedule assumes fixed payments. If you pay extra, the balance column will not match your lender's statement. Fix: add an extra payment column and subtract it from the balance.

Limitations

An amortization schedule built this way assumes a fixed rate and fixed payment for the entire term. It cannot model variable rates, interest-only periods, balloon payments, or fees rolled into the loan without extra columns and formulas. If your loan has any of those features, the schedule will diverge from your lender's numbers.

The schedule also ignores the difference between rounding conventions. Lenders often round each period's interest to cents and carry the difference forward, which can produce a final payment a few cents different from yours. Treat your schedule as a planning tool, not as a legal record of what you owe.

Frequently Asked Questions

What is the PMT formula in Excel?

PMT calculates the constant payment for a loan given a fixed rate and number of periods. The syntax is =PMT(rate, nper, pv, [fv], [type]). For a standard loan, you only need the first three arguments, with pv entered as a negative number so the payment returns positive.

Why does my amortization schedule not end at zero?

The most common causes are a period column that does not run the full term, a balance formula that references the wrong row, or a rate that does not match the payment frequency. Check those three first. A few cents of difference usually comes from rounding.

Can I add extra payments to the schedule?

Yes. Add a column for extra principal, then subtract both the scheduled principal and the extra amount from the previous balance. The loan will pay off earlier, and the final rows will show a zero balance before the original term ends.

How do I handle a loan with a different payment frequency?

Change the payments per year input. The periodic rate becomes the annual rate divided by that number, and the total periods become the term times that number. Everything else in the schedule stays the same.

Should I use IPMT and PPMT or calculate interest manually?

IPMT and PPMT are easier and less error prone. Calculating manually means multiplying the prior balance by the periodic rate for interest, then subtracting that from the payment for principal. Both approaches give the same result if your references are correct.

References

This article draws on the standard references listed under Further Reading.