How to Make a Loan Amortization Schedule in Excel (PMT, IPMT, PPMT and CUMIPMT)

An amortization schedule shows every payment on a loan split into interest and principal, with the balance left after each one. Your lender has one. Building your own takes about ten minutes in Excel. Once you have it you can answer questions the lender's statement does not, like how much interest you will pay in year 7 or when you will owe less than half the original balance.

This guide uses one example the whole way through: a $300,000 loan at 6.75% for 30 years, paid monthly.

Step 1: Put the inputs in their own cells

Keep every assumption in one block at the top so the schedule never contains a typed number.

| Cell | Label | Value | |---|---|---| | B1 | Loan amount | 300000 | | B2 | Annual rate | 6.75% | | B3 | Years | 30 | | B4 | Payments per year | 12 |

Then two helper cells:

Step 2: The payment with PMT

` B7: =PMT(B5, B6, -B1) `

This returns $1,945.79. The loan amount goes in as a negative because Excel treats money you receive as positive and money you pay as negative. Leave the minus sign off and you get the same number with a minus in front, which then flips the sign of every formula below it.

PMT assumes the payment is the same every month and the rate never changes. That fits a fixed-rate mortgage, a car loan or a student loan. It does not fit an ARM after its reset or a loan with a balloon payment.

Step 3: The schedule, three formulas

Start the table at row 10 with these headers: Month, Payment, Interest, Principal, Balance.

Row 10 is month 0. Put 0 in A10 and =B1 in E10. That is the starting balance.

Row 11 is month 1:

| Column | Formula | Month 1 result | |---|---|---| | A11 Month | =A10+1 | 1 | | B11 Payment | =$B$7 | 1,945.79 | | C11 Interest | =E10*$B$5 | 1,687.50 | | D11 Principal | =B11-C11 | 258.29 | | E11 Balance | =E10-D11 | 299,741.71 |

Fill row 11 down to row 370 and you have all 360 months. The last balance should be zero. If it is not, one of the absolute references is missing a $.

Interest is always last month's balance times the monthly rate. That one line is the whole engine. Everything else is subtraction.

Step 4: Check it against IPMT, PPMT and CUMIPMT

Excel has built-in functions that compute any single row without the table. Use them as a test.

Add a check column =ROUND(C11-IPMT($B$5,A11,$B$6,-$B$1),6) and fill it down. Every row should show 0. If any row does not, your schedule and Excel's math disagree, and that is worth knowing before you trust it.

CUMIPMT is also the fastest way to get one year of mortgage interest for a tax estimate. Year 1 here is $20,152.29 of interest and only $3,197.24 of principal.

Step 5: Read the schedule

A finished schedule has 360 rows and most people scroll it once and close it. These are the three rows worth finding.

The crossover month. Early payments are mostly interest. In month 1, 87% of the $1,945.79 is interest. Principal grows a little every month because the balance shrinks and so does the interest charged on it. The month where principal first beats interest is the crossover:

` =INDEX(A11:A370, MATCH(TRUE, INDEX(D11:D370>C11:C370, 0), 0)) `

Here it is month 238, almost 20 years in. That is why the first decade of a 30-year loan builds equity so slowly. After 10 years you have paid $233,495 in total payments. Only $44,097 of that reduced the balance (300,000 minus the month 120 balance of 255,903). The other $189,398 was interest.

The halfway month. When do you owe less than $150,000?

` =INDEX(A11:A370, MATCH(TRUE, INDEX(E11:E370<=B1/2, 0), 0)) `

This returns month 259, about 21.6 years in. The natural guess is 15 years, the midpoint of the term, and it is off by more than six years. The gap is the same front-loading effect. At month 180 the balance is still $219,886, which is 73% of the loan.

Total interest. =SUM(C11:C370) gives $400,485.94. You borrow $300,000 and repay $700,485.94. At 6.75% the interest is more than the loan.

| Point in the loan | Interest so far | Balance | |---|---|---| | Month 12 | 20,152.29 | 296,802.76 | | Month 60 | 98,374.79 | 281,627.13 | | Month 120 | 189,398.31 | 255,903.00 | | Month 180 | 270,129.23 | 219,886.26 | | Month 240 | 336,449.31 | 169,458.68 | | Month 360 | 400,485.94 | 0.00 |

Step 6: Rounding, the last-payment problem

The table above keeps full precision. A real lender rounds the payment to the cent and rounds each month's interest to the cent. Change B7 to =ROUND(PMT(B5,B6,-B1),2), add ROUND(...,2) to the interest and balance columns and run it again. The schedule now ends with $5.29 still owed after payment 360.

That is not an error in your sheet. It is why the final payment on a real loan is usually a few dollars different from the rest. Handle it in the payment column so the last row clears the balance:

` B11: =IF(A11=$B$6, E10+ROUND(E10*$B$5,2), $B$7) `

If you plan to compare your schedule against a lender statement, use the rounded version. If you only want totals, full precision is fine. The difference over 30 years is a few dollars.

Step 7: Two quick what-ifs

The schedule makes trade-offs visible.

Shorter term. Change B3 to 15 and the rate to a typical 15-year rate of 6.25%. The payment rises to $2,572.27 and total interest falls to $163,008.35. That is $237,477.59 less interest for $626.48 more per month.

Extra principal. Add an Extra column and subtract it in the balance formula. $200 a month extra pays the loan off in 276 months instead of 360 and cuts total interest to $290,558.43. The full method, including the three mistakes that make most payoff calculators overstate the savings, is in the mortgage extra payment guide.

Common mistakes

Where this fits

The sheet above is the core of every loan payoff calculator. If you want the finished version with extra payments, a lump sum input, a biweekly view, a refinance break-even comparison and balance and interest charts already built, the Numbersmith mortgage and loan payoff calculator has all of it on open formulas.

Written with AI assistance and checked by recalculation. For education and planning only, not financial advice.