Amortization Schedule in Excel: Build a Loan Table

An amortization schedule answers one question with a table: on every payment, how much of your money goes to interest and how much actually reduces the loan. Lenders show you a single monthly payment. The schedule shows you where it goes, month by month, until the balance reaches zero.

Excel builds one in about five minutes once you know which functions to use and which convention to follow. The formulas are simple. The mistakes are predictable, and there are only about five of them. This walks through the whole thing, including the part most guides skip: how commercial real estate loans amortize over one period but come due on another, and how to model that balloon.

How do I create an amortization schedule in Excel?

Put the loan inputs in three cells, calculate the payment once with PMT, then build six columns: period, beginning balance, payment, interest, principal, and ending balance. Each row's interest is the beginning balance times the monthly rate. Principal is the payment minus that interest. The ending balance is the beginning balance minus the principal, and it becomes the next row's beginning balance. Fill down for the number of payments and the last ending balance lands on zero.

That is the whole method. Everything else is detail. Set it up like this, with the inputs anchored so they do not drift when you fill down:

Notice the two conversions inside PMT. The rate is divided by 12 and the term is multiplied by 12, because the rate and the number of periods must use the same unit of time. Microsoft states this plainly in its own documentation: for monthly payments on a four year loan at a 12 percent annual rate, you use 12%/12 for rate and 4*12 for nper. Forgetting one half of that conversion is the single most common error in the whole exercise, and it does not produce an obviously wrong number. It produces a plausible one.

What is the formula for an amortization schedule?

There are two ways to build the body of the table and they agree with each other. The balance driven method computes interest from the running balance. The function method uses IPMT and PPMT to ask Excel directly. Assuming period 1 starts on row 7:

IPMT and PPMT take the same arguments as PMT with one addition, the period number, which slots in second: IPMT(rate, per, nper, pv). For any given period, IPMT plus PPMT equals PMT exactly. That identity is a useful check. If your interest and principal columns do not sum to the payment, something is wrong with an argument, not with Excel.

Two more functions are worth knowing. CUMIPMT and CUMPRINC return the cumulative interest and cumulative principal paid between any two periods, which saves you from summing a column. Watch their argument order, because it is different: CUMIPMT(rate, nper, pv, start_period, end_period, type). There is no per argument, the start and end periods come after pv, and unlike PMT the type argument is required rather than optional. Passing five arguments to a function that wants six is a quiet way to get an error you will stare at for ten minutes.

Why is my PMT result negative?

Because Excel treats money you pay out as a negative number and money you receive as positive. You received the loan, so the principal is positive cash to you, which makes the payment negative cash from you. Microsoft's own example returns a payment of ($1,037.03) in parentheses, which is how Excel renders a negative currency value.

You have two clean fixes and one messy one. Enter the principal as a negative inside PMT, which is the =PMT($B$1/12, $B$2*12, -$B$3) shown above. Or wrap the whole function in a minus sign. The messy option is to leave some formulas signed and others not, which is how a schedule ends up with a principal column that grows the balance instead of shrinking it. Pick one convention and apply it to PMT, IPMT, and PPMT together.

Does Excel have an amortization schedule template?

Yes. Go to File, then New, and search the template box for "amortization schedule" or "loan." Microsoft ships a loan amortization schedule template that asks for the loan amount, rate, term, and start date and generates the payment table for you. Some versions include a column for extra payments.

Use it when you want an answer in thirty seconds. Build your own when the schedule feeds something else, which in practice is most of the time. A template is a closed box. If you need the interest column to flow into a cash flow model, or the ending balance at month 120 to become a balloon in a sale analysis, you want formulas you can see and trace.

How do I add extra payments to an amortization schedule?

Add one column for the extra principal and subtract it in the ending balance: =B7-E7-G7, where G7 holds the additional principal for that period. Everything downstream reacts on its own. The next beginning balance is lower, so next month's interest is lower, so more of the same fixed payment goes to principal, and the loan retires early.

One thing breaks when you do this, and it matters. IPMT, PPMT, CUMIPMT, and CUMPRINC all assume constant payments over the full original term. They know nothing about your prepayments. Once extra principal enters the model, abandon the function method for the interest column and use the balance driven formula, =B7*$B$1/12, which always reflects what is actually owed. Then guard the tail of the table so it stops at zero rather than going negative, with something like =IF(B7<=0, 0, B7*$B$1/12) and the same pattern on principal. Otherwise the schedule keeps paying down a loan that no longer exists.

What is the difference between loan term and amortization period?

The amortization period is the length of time used to calculate the payment. The loan term is how long until the loan is actually due. On a residential 30 year mortgage they are the same number, which is why most people never notice there are two of them. In commercial real estate they are almost never the same.

A typical structure amortizes over 25 or 30 years but matures in 5, 7, or 10. The payment is computed as though you had three decades to pay it off, which keeps it low, and then the entire remaining balance comes due at the end of the term as a balloon. Borrowers refinance or sell before that date rather than write the check. Stretching the amortization lowers the monthly payment and raises the balloon. Shortening it does the reverse, and sharply: on a $1,000,000 loan at 7 percent, one lender's published comparison puts the payment near $7,067 a month at a 25 year amortization against roughly $11,611 at a 10 year, about 64 percent higher for the same money.

How do I calculate a balloon payment in Excel?

Build the schedule on the amortization period, then only fill rows through the term. For a 30 year amortization with a 10 year term, set nper to 30*12 inside PMT so the payment is right, and fill 120 rows. The balloon is the ending balance in row 120. That is it. No separate formula, no adjustment.

If you would rather compute it directly without building the table, take the original principal and subtract the cumulative principal paid across those 120 periods: =$B$3 + CUMPRINC($B$1/12, $B$2*12, $B$3, 1, 120, 0). It is a plus sign because CUMPRINC returns a negative. Both approaches give the same number, and the second one is easier to bury a mistake inside, which is a reason to build the table anyway.

Once you have the balloon, you know what the refinance has to cover. That figure is what the next lender underwrites against, and by then the loan documents, the payoff statement, and the new term sheet are all circulating as PDFs waiting on signatures. Getting them signed and returned without printing anything is its own small workflow.

The five mistakes that produce a wrong schedule

Every broken amortization table I have seen fails in one of these ways.

• Annual rate with monthly periods. Using B1 instead of B1/12 inflates the payment enormously. Using B2 instead of B2*12 does the opposite. Check by confirming the last ending balance is zero.
• Unlocked references. Fill =PMT(B1/12, B2*12, -B3) down a column and it walks off the inputs by row 2. Lock them with dollar signs.
• Sign confusion. Mixing a positive pv with an unsigned IPMT gives an interest column that reduces the balance.
• Off by one on the period. The per argument in IPMT and PPMT runs from 1 to nper. Period 1 uses the original principal as its beginning balance, not the balance after one payment.
• Rounding drift. Rounding the payment for display while computing on the rounded value leaves a few cents outstanding at maturity. Keep full precision in the math and round only the format.

When the loan data arrives as a PDF

Building a schedule from scratch assumes you know the rate, term, and principal. Half the time you are working the other direction: a lender or a servicer sends an existing amortization schedule, a payoff statement, or a loan history as a PDF, and you need it in Excel to check their math or to feed a model.

Retyping a 360 row payment table is not a task anyone should be doing. Run the file through a PDF to Excel converter and the periods, payments, interest, and principal come back in their own columns with the amounts as real numbers. If the figures land as text rather than numbers, Excel will not sum them, and the fix takes ten seconds once you know what you are looking at: our guide on numbers stored as text after a conversion covers it. Older servicers still send scanned printouts, which need OCR before anything is readable at all.

From there the schedule plugs into the rest of the underwriting. The annual debt service, which is twelve times the monthly payment, is the denominator of the debt service coverage ratio. The numerator comes off the property's operating statement. Lenders look at both together, and so should you before you sign anything. If you work with property documents regularly, the real estate conversion workflow covers rent rolls and T12s in the same way.

A schedule you can defend

The value of building it yourself rather than accepting a lender's printout is that you can see every assumption. You know whether the interest column came from the balance or from IPMT. You know whether the balloon reflects a 25 or a 30 year amortization. You know that the last row hits zero, which means the model is internally consistent.

That matters because the loan is usually the largest single line in the analysis, and a schedule that quietly uses the wrong rate convention will still look like a schedule. It will have the right number of rows. It will decline smoothly. It will just be wrong by a few hundred thousand dollars over thirty years, and nobody will catch it, because the shape of the curve looks exactly the way an amortization curve is supposed to look.