Loan Amortization Table Excel: Build It Step by Step

A loan amortization table in Excel shows how each regular payment is divided between interest and principal, along with the remaining balance. Enter the loan amount, annual APR, and term in the calculator below to estimate the monthly payment and total interest; then use the spreadsheet steps to build a payment-by-payment schedule.

Formula

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

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

• P is the starting loan principal, in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200. APR is entered as a percentage per year.
• n is the number of monthly payments, calculated as years × 12.

Total interest in this calculator is monthly payment × number of payments − principal. The calculator displays the payment and total interest to the nearest cent. It assumes the unrounded formula payment for its total-interest output, so a spreadsheet schedule that rounds every payment and interest charge to cents can differ by a few cents. Actual lender schedules can also differ because of payment dates, rounding conventions, fees, or loan terms.

Worked Example

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

1. Convert the APR to a monthly rate: 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Apply the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53 per month, rounded to cents.
4. Calculate total interest using the displayed formula: $405.53 × 60 − $20,000 = $4,331.80.

The payment is a level-payment estimate. In an actual cent-rounded schedule, the final payment may need a small adjustment to bring the balance to zero.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are made monthly, at the end of each period.
• Interest accrues monthly using APR divided by 12.
• The calculation excludes fees, taxes, insurance, PMI, and escrow.

Build the Amortization Table in Excel

Set up a small input area at the top of a worksheet. For example, put the principal in B1, APR as a percentage in B2, and term in years in B3. Enter 8% as a percentage—not 8—so Excel treats the rate as 0.08. Put the monthly payment in B4 using:

=-PMT(B2/12,B3*12,B1)

Excel’s PMT function returns a negative value when the principal is entered as a positive cash inflow. The minus sign displays the payment as a positive amount. This formula assumes a fixed rate and regular end-of-month payments.

Starting in row 7, create these column headings: Payment number, Beginning balance, Payment, Interest, Principal paid, Ending balance. In the first schedule row, use these formulas, adapting cell references to your layout:

• Payment number: enter 1.
• Beginning balance: =$B$1
• Payment: =$B$4
• Interest: =B8*$B$2/12, if B8 contains the beginning balance.
• Principal paid: =C8-D8, if C8 is payment and D8 is interest.
• Ending balance: =B8-E8, if E8 is principal paid.

For the next row, increase the payment number by one and make its beginning balance equal to the previous row’s ending balance. Copy the formulas downward for the full term—60 rows for five years of monthly payments. Format money columns as currency and rates as percentages. Check that the ending balance approaches zero; a small residual can result from rounding, while a growing balance can indicate incorrect references or a payment that does not cover interest.

How to Read the Schedule

Each payment first covers that period’s interest. The rest reduces principal. For a fixed-rate loan with a level payment, the interest portion generally declines as the balance falls, while the principal portion increases. This does not mean the payment itself is changing: it is the allocation within the payment that changes.

For example, the first month’s interest on a $20,000 balance at 8% APR is about $133.33: $20,000 × 0.08 ÷ 12. With a $405.53 payment, roughly $272.20 goes toward principal before cent-rounding effects. The next month’s interest is computed on the lower balance, not on the original $20,000.

Rounding, Extra Payments, and Common Errors

For a schedule that mirrors typical monthly accounting, calculate each period’s interest to cents, for example with =ROUND(beginning_balance*APR/12,2). Then calculate principal as payment minus rounded interest. Because the scheduled payment is rounded too, the final row can leave a small balance or overpay by a few cents. A spreadsheet can adjust the final payment to the remaining balance plus that period’s interest, but the exact handling should match the loan documents.

• Use an APR entered as 8% or 0.08, not the number 8 in a percentage-formatted cell.
• Do not divide a percentage-formatted APR by 100 again; use APR/12 for a monthly rate.
• Keep the term units consistent: years × 12 gives monthly payment count.
• Do not treat the full payment as principal; subtract interest first.
• Use absolute cell references such as $B$2 for fixed inputs before filling formulas down.

An extra principal payment can be added as a separate column and deducted from the balance after the scheduled principal. This changes the payoff path, so it should not be confused with the standard schedule above. Check whether the loan permits the assumed treatment and how the servicer applies extra amounts; the calculator does not model those details.

When This Table Does Not Match a Loan

This method models a straightforward fixed-rate loan with equal monthly periods. It is not a complete statement of a lender’s account. A variable rate, irregular first payment period, daily interest calculation, skipped or late payment, fee, balloon payment, or changing installment can produce a different schedule. APR may also include costs that are not part of the interest rate used to accrue monthly interest, depending on the loan disclosure and applicable calculation method.

For an adjustable-rate loan, the schedule must be updated when the rate changes. For a real payoff quote, use the lender’s current balance and payoff figure rather than relying on a spreadsheet projection. The result is an estimate; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

How do I make an amortization schedule in Excel?
Enter the principal, APR, and term, calculate the payment with PMT, then create monthly rows for beginning balance, interest, principal, and ending balance. Fill formulas down for the number of payments.

What Excel formula calculates a loan payment?
For monthly payments, use =-PMT(APR/12, years*12, principal). Enter APR as a percentage and principal as a positive number. The minus sign makes the payment display as positive.

Why does my amortization table end with a balance?
Common causes include rounding each month, incorrect APR units, a wrong payment count, or a formula reference that shifted while copying. A small final residual may be normal; a large or increasing balance suggests an error or an insufficient payment.

Does an amortization table include taxes and insurance?
Not in the standard principal-and-interest calculation. Taxes, insurance, PMI, escrow, and fees require separate inputs and may be collected alongside a payment without reducing the loan principal.

Can I use the table for extra payments?
Yes, add an extra-principal column and subtract it from the balance after calculating scheduled principal. Confirm how extra payments are applied for the actual loan, because this simple model does not represent lender-specific processing.

Related

• Amortization Table: Annual Schedule Calculator
• 30-Year Fixed Mortgage Amortization Table Calculator
• 30-Year 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