Loan Amortization Table Excel: Formula and Example

A loan amortization table in Excel shows each scheduled payment split between interest and principal, along with the balance remaining after each payment. This calculator estimates a fixed monthly payment and total interest from the loan amount, annual APR, and term; you can use those inputs to build or check a spreadsheet schedule.

Formula

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

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

• P is the starting principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
• n is the number of monthly payments: years × 12.

The calculator displays the payment to cents. Total interest is estimated as the displayed monthly payment multiplied by the number of payments, minus the original principal. Since the payment is rounded to cents, the total is based on that rounded payment, as a spreadsheet schedule ordinarily is. The final lender payment can differ slightly if the lender adjusts it to clear a remaining rounding balance.

Worked Example

Using the calculator defaults—$20,000 borrowed at 8% APR for 5 years:

1. Convert the annual APR to a monthly decimal rate: r = 8 ÷ 1200 = 0.0066666667.
2. Find the number of monthly payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: 20,000 × 0.0066666667 ÷ [1 − (1.0066666667)−60] ≈ $405.53.
4. Calculate estimated total interest using the rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The results are a monthly payment of $405.53 and estimated total interest of $4,331.80.

Assumptions

• The interest rate remains fixed for the full term.
• Payments are made monthly, at the end of each period, with the first payment one month after borrowing.
• Interest is modelled monthly at APR ÷ 12; this is a standard estimate and may not match a lender’s day-by-day accrual method.
• Fees, taxes, insurance, mortgage insurance, and escrow are not included.
• Payments are level and made on schedule; extra payments, missed payments, and rate changes are not modelled.

Build the Amortization Table in Excel

Excel can display the payment schedule row by row. Put the principal, annual APR, and term in separate input cells, then create columns for payment number, beginning balance, payment, interest, principal repaid, and ending balance. For example, if principal is in B1, APR in B2, years in B3, and the first schedule row is row 7, use formulas along these lines:

• Monthly payment: =-PMT(B2/12,B3*12,B1)
• Interest for the first period: =B1*B2/12
• Principal repaid: =payment_cell-interest_cell
• Ending balance: =beginning_balance_cell-principal_cell

Excel’s PMT function expects a periodic rate and number of periods. If the annual APR is entered as 8%, use B2/12, not B2/1200, because Excel stores 8% as 0.08. The negative sign before PMT makes the payment display as a positive amount when the principal is entered as positive. Alternatively, enter the APR as the number 8 and divide by 1200; do not mix that convention with a percentage-formatted input.

For later rows, link the new beginning balance to the previous row’s ending balance and copy the formulas down. The interest amount should gradually decline as the balance falls, while the principal portion generally rises. At the final payment, check for a small residual caused by rounding and adjust that payment if needed rather than assuming every displayed row will sum perfectly.

How to Read and Use the Schedule

Each row represents one payment period. Interest is the cost of borrowing for that period, calculated from the balance subject to interest and the periodic rate. The rest of the payment reduces principal. The ending balance becomes the next period’s beginning balance.

For the example, the first month’s estimated interest is $20,000 × 0.08 ÷ 12 = $133.33. Of the $405.53 payment, about $272.20 reduces principal, leaving approximately $19,727.80. Calculations may vary by a cent depending on whether intermediate values are rounded. A useful spreadsheet check is that principal portions across all periods add up to the original amount borrowed, subject to any final rounding adjustment.

An amortization table is useful for understanding how a fixed payment is allocated over time and estimating the balance after a given number of scheduled payments. It is not, by itself, a payoff quote: a payoff figure may use a different date, accrued interest, fees, or other contract details.

Common Excel Errors and Limits

• Using the annual rate as a monthly rate: Convert the APR to a monthly rate before calculating periodic interest. A percentage-formatted 8% cell and a numeric 8 cell require different formulas.
• Using years as the payment count: For monthly payments, multiply years by 12 in PMT and in the schedule length.
• Rounding every intermediate value: Display currency to two decimals, but retain full precision in formulas unless modelling a lender’s specific rounding method. Rounding each calculation can make the ending balance drift.
• Assuming every loan is fixed-rate: A changing rate, irregular payment, interest-only period, or extra principal payment requires a schedule that reflects those terms.

The calculation also assumes a regular monthly schedule and a nominal APR divided into twelve equal periods. Lenders may calculate interest using exact days, different payment dates, fees, or contract-specific rules. Obtain the actual rate, payment schedule, and payoff information from the lender rather than treating the spreadsheet as a contract statement.

Frequently Asked Questions

What Excel formula calculates a monthly loan payment?
Use =-PMT(APR/12, years*12, loan_amount) when APR is stored as a percentage, such as 8%. The minus sign displays the payment as positive.

How do I calculate interest for each row in Excel?
Multiply that period’s beginning balance by the monthly rate. With an APR stored as 8%, the rate expression is 8%/12; subtract the interest from the payment to find principal repaid.

Why does my Excel balance not reach exactly zero?
Displayed currency is rounded, while spreadsheet formulas may retain additional decimal places. A small residual can also result from the lender’s timing or rounding rules. Check the final payment and avoid rounding intermediate formulas unnecessarily.

Does the table include fees or extra payments?
No. The basic schedule models level payments on a fixed-rate loan and excludes fees, taxes, insurance, and additional payments. Add separate inputs and schedule logic if those amounts need to be modelled.

Is an amortization table the same as a payoff quote?
No. It estimates scheduled balances under stated assumptions. A payoff quote may include interest accrued through a particular date and other contract-specific amounts.

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

Related

• Finance Amortization Table Calculator and Guide
• Free Amortization Table Calculator and Loan Schedule
• Effective Interest Amortization Table: 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