Sample Amortization Schedule Excel: Formula and Example

A sample amortization schedule in Excel shows how each regular loan payment is divided between interest and principal, and how the balance declines over time. Enter a loan amount, annual interest rate, and term to calculate an estimated monthly payment and total interest; the example below uses $20,000 at 8% APR for five years.

Formula

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

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

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

Total interest in this calculator is the sum of the unrounded formula payment across all scheduled months, less principal. The payment output is rounded to cents for display. A real lender’s payment and total may differ because of payment rounding, fees, a different day-count convention, or other contract details. The schedule itself is built by applying the monthly rate to the opening balance, then subtracting the principal portion of the payment.

Worked Example

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

1. Convert the APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066666667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute into the payment formula: $20,000 × 0.0066666667 ÷ [1 − (1.0066666667)⁻⁶⁰] = $405.5279, or $405.53 per month rounded to cents.
4. Apply the calculator’s total-interest expression using the unrounded payment formula: ($405.5279 × 60) − $20,000 = $4,331.67, rounded to cents.

Because the displayed monthly payment is rounded but the total-interest expression uses the formula’s unrounded payment, multiplying $405.53 by 60 gives $24,331.80, which is 13 cents higher than the formula-based total. A real payment schedule normally accounts for cents in each actual payment and may adjust the final payment. This distinction is useful when checking spreadsheet results.

Assumptions

• The interest rate remains fixed for the entire term.
• Payments are monthly, level, and made at the end of each monthly period.
• Interest accrues monthly at APR divided by 12; the calculation does not model daily accrual or irregular payment dates.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

How an Amortization Schedule Works

An amortization schedule is a month-by-month ledger. Each row typically includes the payment number, payment amount, interest, principal repaid, and remaining balance. Interest is calculated on the balance at the start of that period. The principal part is whatever remains after subtracting interest from the scheduled payment.

For the example, first-month interest is $20,000 × 0.0066666667, or about $133.33. Of a $405.53 payment, roughly $272.19 therefore reduces principal, leaving a balance near $19,727.81. In later months, interest is charged on a lower balance, so more of an equal payment goes toward principal. The exact cents can vary depending on how a spreadsheet rounds intermediate calculations.

With a level-payment loan at a fixed rate, the balance should reach approximately zero after the scheduled number of payments. A tiny residual balance or a slightly adjusted last payment can result from rounding. A schedule is not a promise that a lender will use precisely the same calculation method.

Building the Schedule in Excel

Excel’s payment function can calculate the periodic payment, while ordinary cell formulas can produce each schedule row. For example, put the principal, annual rate, and term in separate input cells. In a monthly-payment cell, use =-PMT(annual_rate/12, years*12, principal) when principal is entered as a positive number. The minus sign makes the displayed payment positive under Excel’s cash-flow sign convention.

For the first schedule row, use the starting balance as the opening balance. Calculate interest as opening balance × annual rate ÷ 12; calculate principal as payment minus interest; and calculate closing balance as opening balance minus principal. In each following row, carry the previous closing balance into the opening balance. Fill the formulas down for the planned number of months.

• Keep inputs in clearly labeled cells and use absolute references (such as $B$2) for values that should not move when formulas are copied.
• Format money as currency, but avoid rounding the balance or interest inside formulas unless the intended schedule explicitly rounds each period.
• Check that total principal repaid is approximately the original principal and that the ending balance is approximately zero.
• For actual payment timing or lender-specific rounding, compare the schedule with the loan disclosure or servicer records.

Common Spreadsheet Errors and Limits

A frequent mistake is entering an annual rate such as 8% as 8 in a formula that expects 0.08, or dividing a rate by 12 but forgetting to convert a whole-number percentage into a decimal. Excel stores a percentage-formatted 8% cell as 0.08, so rate/12 is appropriate in that case. If the input cell contains the number 8, divide by 1,200 instead.

Another source of confusion is the term unit. A 60-month term is not the same input as 60 years. Convert years to months for a monthly schedule, and ensure the payment frequency matches the rate period. The formula above assumes monthly payments; it is not directly suitable for weekly or biweekly installments without changing the periodic rate and payment count.

The calculation also assumes a fixed rate and equal payments. A variable rate, interest-only period, skipped payment, extra principal payment, or fee financed into the loan changes the schedule. For additional principal, subtract the extra amount from the balance after calculating the normal period’s interest and payment, while checking that the balance does not fall below zero. A spreadsheet can illustrate the arithmetic, but it cannot establish the lender’s exact payoff amount or account for every contract feature.

Frequently Asked Questions

How do I make an amortization schedule in Excel?
Calculate the monthly payment, then create columns for opening balance, payment, interest, principal, and closing balance. Use each month’s closing balance as the next month’s opening balance and copy the formulas down for the term.

What Excel formula calculates a loan payment?
For monthly payments, use =-PMT(APR/12, years*12, principal) when APR is stored as a percentage such as 8% and principal is a positive amount. Confirm the signs and units match your spreadsheet.

Why does my Excel balance not end at exactly zero?
Small residuals commonly result from rounding displayed values or rounding each period’s interest. Keep full precision in intermediate formulas and round only for display, or adjust the last payment to clear the remaining balance.

Does an amortization schedule include extra payments?
Not unless you add an extra-payment input or column. Extra principal generally reduces the balance sooner and changes later interest, so a schedule with only the regular payment will not reflect that effect.

Is this schedule the same as the lender’s payoff amount?
No. It is a simplified estimate for level monthly payments. A payoff figure may reflect the payment date, accrued interest, fees, and loan-specific terms that this calculation does not include.

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

Related

• Amortization Schedule From a Monthly Payment
• Home Loan Amortization Schedule Calculator
• Amortization Schedule Free? Calculate Loan Payments

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