Amortization Table Calculator in Excel: Formulas

An amortization table calculator in Excel lays out each loan payment by period, showing how much goes to interest, how much reduces principal, and what balance remains. Enter the loan amount, annual interest rate, and term into the calculator below to estimate the monthly payment and total interest; the steps that follow show how to build the full schedule in Excel.

Formula

The fixed monthly payment is calculated using the standard level-payment loan formula:

PMT = P × r ÷ [1 − (1 + r)−n]

• P is the original loan principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR divided by 12 and then by 100.
• n is the number of monthly payments: loan term in years multiplied by 12.
• PMT is the monthly payment, in dollars.

Total interest in the calculator is estimated as the rounded monthly payment multiplied by the number of payments, minus the original principal. Excel’s PMT function uses the same level-payment method. The calculator assumes a positive rate, as indicated by its 0.01% minimum.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% APR, and a five-year term:

1. Monthly rate: r = 8 ÷ 1200 = 0.0066667.
2. Number of payments: n = 5 × 12 = 60.
3. Payment: 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] = $405.53 when rounded to cents.
4. Total interest: $405.53 × 60 − $20,000 = $4,331.80.

The displayed payment and interest are estimates based on the stated formula and rounded payment. A lender’s schedule can differ if its payment amount, dates, rounding, or interest calculation conventions differ.

Assumptions

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

Build an Amortization Table in Excel

A schedule needs one row for each payment period. First place the inputs in clearly labeled cells—for example, principal in B1, annual APR in B2, term in years in B3, and monthly payment in B4. In B4, enter =-PMT(B2/12,B3*12,B1) if B2 contains a decimal rate such as 8% (rather than the number 8). The negative sign makes the payment display as a positive amount under Excel’s cash-flow sign convention.

Create columns for payment number, beginning balance, payment, interest, principal paid, and ending balance. The first schedule row can use these formulas:

• Payment number: 1; in later rows, add 1.
• Beginning balance: =$B$1 in the first row; afterward, link it to the prior row’s ending balance.
• Payment: =$B$4.
• Interest: beginning balance multiplied by the monthly rate, such as =B7*$B$2/12.
• Principal paid: payment minus interest, such as =D7-E7, adjusted to match your column positions.
• Ending balance: beginning balance minus principal paid.

Copy the row formulas down for the full term. For a five-year monthly loan, that means 60 payment rows. In a practical spreadsheet, use absolute references (the dollar signs in formulas such as $B$2) for input cells so they do not move when copied.

How to Read and Check the Schedule

At the beginning of a standard fixed-rate loan, the balance is larger, so the interest portion of the payment is generally larger. As principal is repaid, later periods calculate interest on a smaller balance, leaving more of the same payment to reduce principal. The scheduled payment remains level, except that the last row may need a small adjustment for rounding.

Useful checks include confirming that each row’s payment equals interest plus principal, each ending balance equals beginning balance minus principal, and each new beginning balance matches the prior ending balance. The final balance should be zero or very close to zero. A small residual can occur because spreadsheet cells display rounded values while formulas retain additional decimal places. If you round every row’s interest and principal to cents, the final payment may need to be calculated as the remaining balance plus that period’s interest.

Excel Functions and Common Errors

Excel’s PMT(rate,nper,pv) calculates the regular payment. IPMT(rate,per,nper,pv) calculates interest for a selected period, while PPMT(rate,per,nper,pv) calculates principal for that period. Excel commonly returns these amounts as negative cash flows when the present value is entered as a positive number; use a leading minus sign if you want positive values for presentation. Keep the same sign convention throughout the workbook.

• Entering 8 instead of 8%: If a formula divides the APR by 12, the input should be 8%, or 0.08—not 8. If you enter 8 as a whole number, divide by 1200 to obtain the monthly decimal rate.
• Using years as the payment count: Monthly schedules need years multiplied by 12.
• Mixing payment timing: The usual PMT setup assumes end-of-period payments. Payments at the beginning of each period use a different timing assumption.
• Overlooking extra payments: Additional principal payments change future balances and interest; a basic fixed-payment schedule does not include them unless you add a separate input and formula.
• Rounding every intermediate value: Formatting cells to show two decimals is not the same as rounding the underlying formulas. Choose a consistent approach and check the closing balance.

Limits and When to Use It

An Excel amortization table is useful for understanding a fixed-rate loan’s payment path, checking the effect of a different principal or term, and tracking scheduled principal and interest. It is not automatically a copy of a lender’s official schedule. Variable rates, irregular payment dates, fees financed into the balance, payment changes, skipped payments, and extra payments require additional inputs or different formulas. A zero-interest loan also needs a separate payment calculation because the displayed formula divides by the rate.

Use the lender’s actual principal, APR, term, payment frequency, and disclosed payment amount when comparing a spreadsheet with an account statement. APR can include information that a simple monthly-interest schedule does not model, and lender-specific timing and rounding can affect the figures. This calculator is estimate arithmetic for education, not financial advice; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

How do I make an amortization table in Excel?
Enter loan inputs, calculate the fixed payment with PMT, then create a row per payment with beginning balance, interest, principal, and ending balance. Fill the formulas down through the loan term and check that the last balance closes to zero or a small rounding residual.

What Excel formula calculates a loan payment?
Use =PMT(monthly_rate,number_of_payments,principal). For an APR entered as a percentage and a monthly schedule, monthly_rate is APR divided by 12, and number_of_payments is years multiplied by 12. Excel may show the payment as negative depending on the sign of the principal input.

Why does my Excel balance not reach zero?
Common causes include rounding interest each period, an incorrect payment count, a rate entered in the wrong format, or a payment that does not match the schedule assumptions. Check the final row and, if needed, calculate the last payment from the remaining balance plus that period’s interest.

Does an amortization table include extra payments?
Not unless you build them into the schedule. Add a separate extra-principal column, subtract that amount from the balance, and recalculate subsequent interest and payoff timing. A fixed schedule that ignores extra payments will not reflect their effect.

Is Excel’s amortization schedule the exact amount my lender will charge?
Not necessarily. The model uses stated inputs and simplified monthly calculations; actual schedules may reflect lender-specific dates, rounding, fees, or rate changes. Consult the lender’s disclosure or account information for exact amounts.

Related

• Qualified Income Deduction Phaseout: 2025 Limits
• Qualified Income: Meaning, Examples, and Tax Rules
• Qualified Income Annuity: How It Works

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