Amortization Table in Excel: Formulas and Examples

An amortization table in Excel shows how each scheduled loan payment is divided between interest and principal, and how the balance declines over time. Enter the loan amount, annual APR, and term to estimate a fixed monthly payment and total interest; the schedule below explains how to build the month-by-month table.

Formula

The fixed monthly payment before rounding is M = P × r / [1 − (1 + r)⁻ⁿ]. Here, 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), and M is the monthly payment in dollars. The calculator rounds the payment to cents for display. Its total-interest estimate is rounded monthly payment × number of payments − original principal.

In an Excel worksheet, the equivalent payment formula is =-PMT(apr/12,years*12,loan_amount) when APR is entered as a percentage such as 8%, rather than the number 8. The minus sign makes the result display as a positive payment when the loan amount is entered as a positive value. You can also use the direct formula above, adjusting the rate format to match your cell.

Worked Example

Using the calculator defaults, the principal is $20,000, APR is 8%, and the term is 5 years, or 60 monthly payments.

1. Convert APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
2. Calculate the payment: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53 when rounded to cents.
3. Calculate the scheduled amount paid: $405.53 × 60 = $24,331.80.
4. Subtract the starting principal: $24,331.80 − $20,000 = $4,331.80 total interest.

The actual final payment can differ by a few cents if a lender calculates interest daily or adjusts the last payment. A spreadsheet using the rounded monthly payment may also leave a tiny residual balance from rounding.

Assumptions

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

Build the Schedule in Excel

Put the principal, APR, and term in separate input cells, for example B1, B2, and B3. Use B4 for the monthly payment formula. Keep the APR cell formatted as a percentage, such as 8%, and use a number of years in B3. In the schedule, create columns for payment number, beginning balance, payment, interest, principal, and ending balance.

1. In the first schedule row, enter payment number 1 and set beginning balance to the original loan amount.
2. Calculate interest as beginning balance × APR ÷ 12. For example, if the beginning balance is in B8 and APR is in B2, enter =B8*$B$2/12.
3. Set payment to the monthly payment cell, such as =$B$4.
4. Calculate principal paid as payment − interest.
5. Calculate ending balance as beginning balance − principal paid.
6. For the next row, link beginning balance to the prior row’s ending balance, increase the payment number by one, and copy the formulas down for the full term.

Use absolute references (the dollar signs in $B$2) for cells that must remain fixed as formulas are copied. Format money columns as currency with two decimal places. If the payment amount is rounded to cents, the last scheduled row may need a final-payment adjustment so the balance ends at zero rather than a small positive or negative amount.

How to Read and Check the Results

At the start of a fully amortizing loan, the balance is largest, so the interest portion of a level payment is generally larger. As the balance falls, the interest charge falls and more of each unchanged payment goes toward principal. Each row should satisfy payment = interest + principal, and beginning balance − principal = ending balance. The ending balance in one row should equal the beginning balance in the next.

For a basic check, sum the interest column and compare it with the calculator’s estimated total interest. Small differences can arise because the table rounds each month’s interest or payment, while the calculator uses a rounded payment times the number of payments. Excel’s ROUND function can make rounding explicit, but the lender’s method and payment dates determine the exact figures.

Common Errors and Limits

• Entering APR in the wrong format: If B2 contains 8%, divide by 12; if it contains 8, divide by 1,200. Mixing these formats produces a dramatically incorrect result.
• Using the wrong sign in PMT: Excel treats cash paid and cash received as opposite signs. A negative loan amount or a minus sign before PMT can change the displayed sign.
• Confusing APR with a periodic rate: The schedule needs a monthly rate. Do not enter an annual percentage directly as the monthly rate.
• Expecting every real loan to match: Variable rates, daily interest, irregular payment dates, fees, extra payments, and lender rounding are outside this simple fixed-payment model.

An amortization table is a projection, not a statement of the balance a lender will report on a particular date. For a variable-rate loan, a new rate or payment may require rebuilding the schedule from the updated balance. Extra payments also change the path: if an extra amount is applied to principal, later interest is generally calculated on a lower balance, but the lender’s allocation rules matter.

Frequently Asked Questions

What Excel formula calculates a monthly loan payment?
Use =-PMT(APR/12, years*12, principal) when APR is entered as a percentage and principal is positive. The minus sign displays the payment as a positive amount.

How do I calculate interest for each row?
Multiply the row’s beginning balance by the monthly rate. With APR stored as 8%, use beginning balance × APR ÷ 12.

Why does my ending balance not reach zero?
Rounding each payment or interest charge can leave a small residual. Adjusting the final payment to the remaining balance plus that period’s interest is one way to close the schedule, subject to the lender’s actual calculation.

Can I include extra monthly payments?
Yes. Add an extra-principal column and subtract it from the balance along with the scheduled principal. Do not let the balance go below zero; cap the final payment at the remaining amount due, and verify how the lender applies extra funds.

Does the table include taxes and insurance?
No. This schedule models principal and interest only. Entered payment figures do not automatically include taxes, insurance, PMI, escrow, or fees.

This calculator is educational estimate arithmetic, not financial advice; the result is an estimate and the lender’s disclosure gives the exact figures.

Related

• Amortization Table With Fixed Monthly Payments
• Amortization Table With Extra Payments in Excel
• Student Loan Amortization Table Calculator

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