Loan Amortization Excel: Formula and Schedule

A loan amortization Excel sheet shows how each scheduled payment is divided between interest and principal, and how the balance declines over time. Enter a loan amount, annual APR, and term in the calculator below to estimate the monthly payment and total interest; an Excel schedule can then show the month-by-month breakdown.

Formula

For a fixed-rate loan repaid in equal monthly payments, the payment is calculated from the present value of those payments:

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

• P is the amount borrowed, in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200 when APR is entered as a percentage per year.
• n is the number of monthly payments, calculated as loan term in years × 12.
• Payment is the monthly amount in dollars, rounded to the nearest cent.

Total interest in this calculator is monthly payment × number of payments − original loan amount. The payment is rounded to cents first, and that rounded payment is used for the total. Real statements may differ slightly if payment dates, daily interest, rounding, or the final payment amount differ from these assumptions.

Worked Example

Using the calculator’s defaults—$20,000 borrowed, 8% APR, and five years:

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

The estimated monthly payment is $405.53, and estimated total interest is $4,331.80. This is the constant-payment estimate; an amortization table may adjust the last payment to bring the remaining balance exactly to zero.

Assumptions

• The interest rate is fixed for the full term.
• Payments are equal, made monthly, and treated as occurring at the end of each monthly period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, mortgage insurance, escrow, and any extra or missed payments are not included.

Build an Amortization Schedule in Excel

Excel can turn the payment estimate into a row-by-row schedule. Put the principal, annual APR, and term in separate input cells. For example, use B1 for the loan amount, B2 for APR entered as a percentage (such as 8%), and B3 for the number of years. A payment formula can be written as =-PMT(B2/12,B3*12,B1). Excel’s PMT function returns a negative cash-flow value when the loan amount is positive, so the leading minus sign displays the payment as a positive number.

Set up columns for payment number, opening balance, payment, interest, principal, and closing balance. In the first schedule row, use these relationships:

• Opening balance: the original loan amount.
• Interest: opening balance × APR ÷ 12.
• Principal repaid: payment − interest.
• Closing balance: opening balance − principal repaid.

For each following row, carry the previous closing balance into the new opening-balance cell. Continue for the full number of monthly payments. If the loan has extra payments, include them as a separate column and subtract them from the balance, while checking that the payment does not exceed what is owed. A schedule should not show a negative balance: adjust the final payment to no more than the remaining principal plus that period’s interest.

How to Read the Schedule

At the beginning of a standard amortizing loan, the balance is larger, so more of a level payment goes to interest. As principal is repaid, the balance shrinks and the interest portion generally falls; the principal portion generally rises. The payment itself stays level in the fixed-rate calculation, though actual lender schedules can have different payment timing or rounding conventions.

For instance, at the first month in the example, estimated interest is $20,000 × 8% ÷ 12 = $133.33. Of the $405.53 payment, about $272.20 reduces principal, leaving an estimated balance of $19,727.80. The displayed figures are rounded to cents; internal calculations with more decimal places can produce a few cents’ difference across a full schedule.

Common Excel Errors and Limits

• APR entered in the wrong form: if B2 contains 8 rather than 8%, divide by 1,200; if it contains 8%, use B2/12. Mixing these conventions produces a radically different result.
• Term units do not match: a term entered in years must be multiplied by 12 for monthly periods. A term already entered as months must not be multiplied again.
• Sign conventions: PMT uses positive and negative cash flows. A minus sign can make the payment easier to read, but keep the signs consistent in any additional Excel functions.
• Rounding every intermediate cell: rounding interest or principal too early can accumulate differences. Keep full precision in formulas and format cells to display cents; follow the lender’s rounding method when exact statement matching matters.
• Assuming every loan is level-payment: variable rates, interest-only periods, irregular payments, fees added to principal, and payment-date rules need different inputs or formulas.

This calculation is a planning estimate, not a statement of the amount due. It does not model lender-specific day-count methods, fees, rate changes, or payment posting dates. Get the actual APR, payment terms, and schedule assumptions from the lender’s disclosure.

Frequently Asked Questions

How do I calculate a loan payment in Excel?
Use =-PMT(APR/12, years*12, loan_amount) when APR is stored as a percentage such as 8%. Confirm the sign convention and that the payment period and rate period are both monthly.

What is the Excel formula for loan amortization?
Use PMT for the level payment, then calculate each period’s interest as opening balance × monthly rate, principal as payment − interest, and closing balance as opening balance − principal.

Why does my Excel balance not reach zero?
Small differences can result from rounding displayed amounts, using a different APR convention, or entering the wrong number of periods. Keep full precision in the schedule and adjust the final payment to the remaining amount due under the lender’s method.

Does an amortization schedule include extra payments?
Not unless you add an extra-payment input or column. Subtract an extra payment from the balance in the period it is made, and recalculate later interest from the reduced balance. Loan terms may affect how additional amounts are applied.

Is Excel’s PMT result the exact payment a lender will charge?
Not necessarily. PMT models a fixed rate and regular payment periods; the lender’s disclosed figures can reflect its own rounding, dates, fees, and other contract details.

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

Related

• Balloon Loan Amortization Schedule Calculator
• Auto Loan Amortization: Payments, Interest, and Balance
• 5 Year Loan Amortization Calculator and Payment Formula

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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2