Amortization Table in Excel: Formula and Example

An amortization table in Excel shows how each loan payment is divided between interest and principal, and how the balance changes over time. This calculator estimates the monthly payment and total interest from a loan amount, annual APR, and term; you can use those results to build or check a spreadsheet schedule.

Formula

For a fixed-rate loan with monthly payments, the level payment before rounding is PMT = P × r / (1 − (1 + r)−n). Here, P is the starting principal in dollars, r = APR / 1200 is the monthly interest rate as a decimal, and n = years × 12 is the number of monthly payments. The calculator rounds the payment to cents. Total interest is calculated as payment × n − P, using that rounded payment.

Excel’s PMT function uses a related form: =PMT(annual_rate/12, years*12, -loan_amount). For the default example, that is =PMT(8%/12,5*12,-20000). The negative sign makes the displayed result positive under Excel’s cash-flow sign convention. The spreadsheet’s payment may differ by a cent from a schedule that rounds interest and principal separately each month; spreadsheet construction details matter.

Worked Example

Using the calculator’s defaults: principal P = $20,000, APR 8%, and term 5 years.

1. Convert APR to a monthly decimal rate: r = 8 / 1200 = 0.0066666667.
2. Convert the term to payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: 20,000 × 0.0066666667 / (1 − (1.0066666667)−60), which is approximately $405.53 after rounding to cents.
4. Calculate total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

• The interest rate remains fixed for the full term.
• Payments are made monthly at the end of each period.
• Interest is modelled monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and changes in payment timing are not included.

Build an Amortization Table in Excel

A basic worksheet has one row for each payment. Put the starting loan balance in the first row, then calculate each period’s interest from that row’s opening balance. Apply the payment to interest first; the remainder reduces principal. The closing balance becomes the next row’s opening balance.

1. Create input cells for loan amount, annual APR, term in years, and monthly payment. Keep the rate as a percentage, such as 8%, and use APR/12 in the monthly calculation.
2. Add schedule columns for payment number, opening balance, payment, interest, principal, and closing balance.
3. In the first schedule row, set opening balance equal to the loan amount. Calculate interest as opening balance × APR / 12.
4. Calculate principal as payment − interest and closing balance as opening balance − principal.
5. For the next row, link opening balance to the previous row’s closing balance, and fill the formulas down for the number of monthly payments.

For example, the first month on a $20,000 balance at 8% APR has interest of about $20,000 × 0.08 / 12 = $133.33. If the payment is $405.53, about $272.20 goes to principal, leaving a balance near $19,727.80. Later interest charges shrink as the balance falls, so more of a level payment goes toward principal.

How to Read and Check the Schedule

Each row describes one payment period. The interest column is the borrowing cost for that period under the model; principal is the amount that reduces the debt. The closing balance should generally decline, reach zero after the final payment, and never become materially negative. Small discrepancies can arise from cent-level rounding or from how the lender handles payment dates and interest accrual.

A useful check is to add the principal amounts across all rows: their sum should equal the original loan amount, subject to any rounding adjustment in the final row. The sum of the interest column should agree with total payments minus principal. Do not confuse the monthly payment with total interest: the payment includes both principal repayment and interest.

Excel Details and Common Problems

• APR entered incorrectly: If a cell contains 8%, Excel stores it as 0.08. Do not divide that value by 100 again. Use either APR/12 for a percentage-formatted cell or 8%/12.
• Years versus months: The schedule needs years × 12 periods. Using five periods instead of 60 gives a different and incorrect result for a five-year monthly loan.
• Rounding every intermediate value: Displaying two decimals is not the same as rounding stored calculations to two decimals. A lender’s exact method may round interest each period; that can produce a slightly different final payment or total.
• Final balance below zero: A small negative amount often signals rounding. Adjust the last payment to the amount needed to clear the remaining balance rather than treating a large negative balance as normal.
• Variable rates or irregular payments: A fixed-payment schedule does not model rate resets, skipped payments, extra principal, or a different payment frequency without additional formulas and inputs.

What an Amortization Table Leaves Out

This standard calculation is an estimate, not necessarily a lender’s payment ledger. Actual loan documents can specify different day-count conventions, payment dates, rate changes, fees, or rules for applying extra payments. A spreadsheet that includes only principal and interest will not show the full amount due if a loan also has taxes, insurance, escrow, or other charges.

Extra payments can be modelled by adding them to the principal reduction for the relevant period, but keep them separate from the scheduled payment and confirm the intended treatment with the lender. If a payment is less than that month’s interest, the balance may grow instead of fall; the simple schedule should not conceal that possibility. Use the lender’s statement or disclosure to verify contractual figures.

Frequently Asked Questions

What Excel formula calculates a loan payment?
Use =PMT(APR/12, years*12, -loan_amount) for monthly payments on a fixed-rate loan. Confirm that the APR cell is represented as a percentage and that the term is converted to months.

How do I calculate interest for each row?
Multiply the opening balance by the monthly rate. With APR stored as a percentage, the formula is typically =opening_balance*APR/12.

Why does my Excel schedule end with a small balance?
Rounding payments or monthly interest to cents can leave a small residual. Differences in payment timing or rounding conventions can also matter. A schedule may adjust the final payment to clear the remaining balance.

Does an amortization table include taxes and insurance?
Not unless you add separate inputs and columns. The standard schedule covers principal and interest only; other charges are not part of this calculator.

Can I use this table for a variable-rate loan?
Not as a complete projection. A fixed-rate table assumes the same APR throughout; variable-rate periods require updating the rate and recalculating the payment or schedule according to the loan terms.

This result is an estimate for educational use; the lender’s disclosure gives the exact figures.

Related

• Qualifying Income: Is It Taxable?
• MTD Qualifying Income: What Counts for a Mortgage?
• Qualifying Relative Income Test: The IRS Limit Explained

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2