Amortization Schedule Calculator in Excel: Formula and Example

An amortization schedule calculator in Excel shows how each fixed loan payment is divided between interest and principal, and how the balance changes over time. Enter the loan amount, annual interest rate, and term to estimate the monthly payment and total interest; the schedule can then list those amounts for every month.

Formula

The calculator uses the standard fixed-payment loan formula. It calculates a monthly payment from the starting principal, monthly interest rate, and number of monthly payments:

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

• P is the amount borrowed, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 1,200. For an 8% APR, r = 8 ÷ 1,200.
• n is the number of monthly payments: loan term in years × 12.

Total interest in this calculator is the rounded monthly payment multiplied by the number of payments, minus the original principal: Total interest = payment × n − P. The payment is rounded to cents before that total is calculated, matching the calculator’s displayed figures. This is a summary calculation; a month-by-month schedule may differ slightly if a lender adjusts the final payment to account for cents rounding.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% annual APR, and a five-year term—the calculation has 60 monthly payments. The monthly rate is 8 ÷ 1,200 = 0.0066667, and the payment formula is:

$20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰] = $405.53 per month after rounding to cents.

Total interest is calculated using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80. The total of the scheduled payments is $24,331.80. These figures are estimates under the stated formula and assumptions, not a lender’s payoff quote.

Assumptions

• The APR stays fixed throughout the term.
• Payments are level monthly payments made at the end of each monthly period.
• Interest accrues monthly using APR divided by 12.
• Fees, taxes, insurance, PMI, and escrow are not included.

How to Build the Schedule in Excel

Excel can calculate the payment and then apply each payment to a row in a table. Put the loan amount, annual APR, and term in separate cells so formulas can refer to them. For example, suppose the principal is in B1, APR is in B2 as a percentage, and the term in years is in B3. A payment formula is =-PMT(B2/12,B3*12,B1). PMT returns a negative value when the loan amount is positive under Excel’s cash-flow sign convention; the leading minus sign displays the payment as a positive amount.

For a transparent schedule, create columns for payment number, starting balance, payment, interest, principal, and ending balance. In the first monthly row, use formulas equivalent to these:

• Starting balance: the original principal for month one; thereafter, the prior row’s ending balance.
• Interest: starting balance × APR ÷ 12.
• Principal paid: payment − interest.
• Ending balance: starting balance − principal paid.

Copy the monthly formulas down for the full number of payments. Excel’s IPMT and PPMT functions can also calculate the interest and principal portions for a particular payment period; their period argument runs from 1 through the total payment count. Check the sign conventions in the resulting cells, because these functions may return cash outflows as negative values.

How to Read the Results

Early in a fully amortizing loan, more of each level payment generally goes to interest because the balance is largest. As the balance declines, the interest portion typically falls and more of the same payment goes to principal. The schedule’s ending balance should approach zero after the final payment, subject to rounding. A large leftover balance, a negative balance, or a final payment that does not reconcile can indicate a wrong cell reference, inconsistent units, or a rounding difference.

The calculator’s total-interest figure is useful for understanding the interest implied by the inputs over the full term. It does not mean the borrower will necessarily make every scheduled payment: early repayment, missed or late payments, rate changes, or lender-specific calculations can change actual interest and balances. Obtain the actual APR, payment details, and fees from the lender’s disclosure rather than treating example inputs as a quote.

Common Excel Errors and Limits

• Entering APR in the wrong form: If a cell is formatted as a percentage, enter 8% (or 0.08), not 8 as a decimal rate. Divide an annual rate by 12 for a monthly rate.
• Mixing years and months: The period count must be the number of monthly payments. A five-year term is 60 periods, not 5.
• Rounding every intermediate value: Rounding the balance or interest in each row can accumulate differences. Keep full precision in formulas and format displayed cells to two decimals, unless reproducing a lender’s specific rounding method.
• Using the wrong payment timing: The formulas here assume payments at period end. Payment timing and daily-interest methods can change a real loan’s figures.
• Assuming every loan has fixed payments: Variable rates, interest-only periods, balloon payments, fees financed into principal, and irregular payment dates are not represented by this simple schedule.

Excel’s built-in PMT function is convenient, while a row-by-row schedule makes each month’s interest and principal visible. Either method depends on accurate loan terms and a consistent rounding approach. This calculator is estimate arithmetic for education, not financial advice; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

What formula does Excel use for an amortization schedule?
For a fixed monthly payment, Excel’s PMT(rate, nper, pv) function uses the periodic rate, total number of payments, and present value. With an annual APR and monthly payments, use APR divided by 12 and a term in years multiplied by 12.

How do I calculate the interest portion of each payment?
Multiply that period’s starting balance by the monthly rate. In a schedule row, interest equals starting balance × annual APR ÷ 12, assuming monthly accrual at APR/12.

Why does Excel show a negative payment?
Excel’s financial functions use signs to distinguish money received from money paid. A positive loan amount can therefore produce a negative payment. Use a leading minus sign or enter the loan amount with the opposite sign to display the payment as positive.

Why is my final balance not exactly zero?
Small differences usually come from rounding or a formula reference error. Keep full precision in intermediate calculations, confirm the period count and rate, and recognize that a lender may adjust the final payment for cents rounding.

Does an amortization schedule include taxes and insurance?
No. A principal-and-interest schedule does not include taxes, insurance, PMI, escrow, or fees unless they are separately entered and modelled. Check the loan documents for the actual payment components.

Related

• Mexico Interest Rate: What It Means and How It Works
• Interest Rate Lock: What It Means and How It Works
• 15 Year Amortization Schedule 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