Insert an Amortization Table in Excel: Steps and Formula

To insert an amortization table in Excel, enter the loan amount, annual interest rate, and term, then calculate each month’s payment, interest, principal, and remaining balance in rows. This calculator estimates the fixed monthly payment and total interest from those inputs; it does not create an Excel file, but the formula and steps below show how to build the table yourself.

Formula

The fixed monthly payment for a fully amortizing loan is calculated as:

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

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: APR ÷ 1,200. The calculator’s APR input is a percentage per year.
• n is the number of monthly payments: loan term in years × 12.

Total interest in the calculator is monthly payment × number of payments − principal. Because the payment is rounded to cents, this total uses that rounded payment, as a spreadsheet schedule generally does when displaying currency. Actual loan schedules may adjust the last payment to clear a small remaining balance.

Worked Example

Using the calculator’s default inputs—$20,000 principal, 8% APR, and 5 years—the number of monthly payments is 5 × 12 = 60. The monthly rate is 8 ÷ 1,200 = 0.0066667.

Payment = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53 per month after rounding to cents. Total interest = $405.53 × 60 − $20,000 = $4,331.80. These are estimates under the stated fixed-rate, monthly-payment assumptions.

Build the Table in Excel

Set up columns for payment number, beginning balance, payment, interest, principal, and ending balance. Put the loan inputs in separate cells and label them clearly; for example, use B1 for principal, B2 for annual APR as a decimal, and B3 for the number of years. If APR is entered as 8%, Excel stores it as 0.08. In the first schedule row, assume row 7, enter payment number 1 and link the beginning balance to the principal cell.

1. Calculate the fixed payment. In a payment input cell, use =-PMT(B2/12,B3*12,B1). Excel’s PMT function returns a negative cash flow when the loan amount is positive, so the leading minus sign displays the payment as a positive amount. Alternatively, use the formula above.
2. Calculate interest for the row. Multiply the beginning balance by the monthly rate, such as =B7*$B$2/12 if B7 contains that row’s beginning balance. Adjust cell references to match your layout.
3. Calculate principal repaid. Subtract interest from the payment: =$B$4-C7, where B4 is the payment and C7 is the row’s interest.
4. Calculate the ending balance. Subtract principal repaid from beginning balance. Link the next row’s beginning balance to the previous row’s ending balance, then increment the payment number.
5. Fill the formulas down. Continue for the full number of monthly payments. Format money columns as currency and check that the final balance is zero or very close to zero.

Use absolute references, such as $B$2, for input cells that must stay fixed when formulas are copied. The exact cell addresses are up to you; the important part is that each row uses the prior balance and the same monthly rate and scheduled payment.

How to Read an Amortization Schedule

Each payment is divided between interest and principal. Interest is calculated on the balance at the start of that period, so the interest portion generally decreases as the balance falls. With a fixed payment, the principal portion generally grows over time. For example, at the start of the default example, monthly interest is about $133.33 ($20,000 × 8% ÷ 12); the remainder of the $405.53 payment reduces principal.

The ending balance for one row becomes the beginning balance for the next. Adding the principal column across all rows should return approximately the original principal, while adding interest gives the schedule’s total interest. Small differences can result from rounding individual rows to cents.

Common Spreadsheet Problems and Limits

• Entering 8 instead of 8%. If the cell is meant to contain a decimal rate, enter 0.08 or format the cell as a percentage and enter 8%. A rate entered as 8 in a formula expecting a decimal is 800%.
• Mixing annual and monthly units. Divide the annual percentage rate by 12 when it is stored as a decimal, or by 1,200 when it is entered as a whole percentage such as 8. Multiply years by 12 to get monthly periods.
• Using the wrong payment sign. Excel’s PMT function follows cash-flow signs. If the result is negative, use a minus sign before PMT for a positive displayed payment.
• Rounding too early. Keep the underlying formulas at full precision and format cells to show two decimals. Rounding every intermediate value can accumulate small differences. A lender may adjust the final payment according to its schedule.
• Assuming every loan is fixed-rate and fully amortizing. This table models equal monthly payments at an unchanged rate. Variable rates, irregular payment dates, fees added to principal, interest-only periods, balloon payments, and extra payments need additional inputs or different formulas.

An amortization table is a schedule, not a forecast of every amount billed. Check the loan documents or lender’s figures for the actual payment timing, rate treatment, fees, and payoff amount. Enter the figures that apply to the loan rather than treating the example as a current rate or offer.

Assumptions

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

Frequently Asked Questions

Can Excel create an amortization schedule automatically?
Yes. You can use Excel’s PMT function for the payment and formulas in adjacent columns for each period’s interest, principal, and balance. Excel versions may also offer loan-amortization templates, but a formula-based table makes the assumptions visible.

What is the Excel formula for a loan payment?
For a monthly fixed-rate loan, use =-PMT(annual_rate/12,years*12,principal) when the annual rate is entered as a decimal and the principal is positive. For example, 8% is entered as 0.08.

Why does my amortization table end with a small balance?
Displayed values may be rounded to two decimals while the formulas retain more precision, or the row formulas may use a different rate, payment count, or sign. Verify the inputs and formulas; a schedule that rounds every row can also need a small final-payment adjustment.

Does an amortization table include extra payments?
Not unless you add them. An extra-payment column should reduce principal in the period it is paid, which changes later interest and can shorten the payoff schedule. The fixed-payment calculator above does not model extra payments.

Is APR the same as the monthly interest rate?
No. APR is expressed per year. This calculator converts it to a monthly rate by dividing the percentage by 12, or a decimal rate by 12. A lender’s exact calculation may reflect terms and charges not represented in this simplified schedule.

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

Related

• Car Loan Amortization Table Calculator
• HELOC Amortization Table Calculator and Payment Guide
• 15-Year Loan Amortization Table and 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