Generate an Amortization Schedule in Excel

An amortization schedule in Excel shows how each regular loan payment is divided between interest and principal, and how the balance declines over time. Enter the loan amount, annual percentage rate (APR), and term below to estimate the monthly payment and total interest; the schedule can then be built with spreadsheet formulas.

Formula

The monthly payment is the fixed-payment loan formula: M = P × r ÷ [1 − (1 + r)⁻ⁿ]. Here, M is the monthly payment in dollars, P is the original principal in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. Convert an APR entered as a percentage to a monthly decimal with r = APR ÷ 1200; calculate the payment count as n = years × 12.

For each row of a schedule, calculate interest as beginning balance × r, principal paid as payment − interest, and ending balance as beginning balance − principal paid. The next row’s beginning balance equals the preceding row’s ending balance. Total interest is the sum of the interest column. This calculator uses the unrounded payment expression in its displayed total-interest formula; a practical Excel schedule normally rounds each payment to cents, so its final total can differ slightly.

Worked Example

Using the calculator’s default values: principal P = $20,000, APR = 8%, and term = 5 years.

1. Convert the rate: r = 8 ÷ 1200 = 0.0066667 per month.
2. Find the number of payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: M = 20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] ≈ $405.53 per month.
4. Using the calculator’s unrounded payment before display rounding, total interest is approximately $4,331.88: payment × 60 − $20,000.

The displayed monthly payment is rounded to cents, but the total-interest output uses the payment expression before that display rounding. In Excel, using a cents-rounded payment for all 60 rows gives a slightly different accumulated total; adjust the final payment to clear any small residual balance.

Build the Schedule in Excel

Create columns for payment number, beginning balance, payment, interest, principal, and ending balance. Put the loan inputs in separate cells—for example, principal in B1, APR in B2, and term in years in B3—so formulas can refer back to them. Use the following setup, changing cell references if your layout differs:

• Payment number, first row: 1; subsequent rows add 1.
• Beginning balance, first row: =$B$1; later rows link to the previous ending balance.
• Payment: =ROUND(-PMT($B$2/1200,$B$3*12,$B$1),2). Excel’s PMT function returns a negative value for a loan amount entered as positive, so the leading minus sign displays the payment as positive.
• Interest: =BeginningBalance*$B$2/1200, using the row’s beginning balance cell.
• Principal: =Payment-Interest.
• Ending balance: =BeginningBalance-Principal.

Copy the row formulas down for the number of payments. For five years of monthly payments, that means 60 rows. Sum the interest column for total interest, and sum the payment column for total paid. With cent-rounded payments, check the last ending balance: rounding can leave a small amount or produce a small overpayment. A common approach is to make the final payment equal to the remaining balance plus that period’s interest, rather than blindly repeating the regular payment.

How to Read the Results

Early in a level-payment loan, the balance is comparatively high, so a larger share of the payment goes to interest. As principal is repaid, interest for each period generally falls and more of the same payment goes to principal. For example, at the start of the default example, first-month interest is about $133.33 ($20,000 × 8% ÷ 12); with a $405.53 payment, about $272.20 goes toward principal. This is why a payment amount alone does not show how quickly the balance falls.

A schedule is useful for seeing the modeled balance on a particular payment date, comparing the principal and interest portions over time, or checking how total interest is accumulated under stated assumptions. It is not a statement of the amount due on a real account: payment dates, daily interest methods, fees, rounding, and lender-specific handling can change actual figures.

Common Problems and Limits

• Entering 8 instead of 0.08: If the APR is entered as a percentage, use APR ÷ 1200 for a monthly decimal rate. Dividing by 12 alone gives 0.6667 rather than 0.006667 when the input is 8.
• Mixing years and months: The payment count for a term in years is years × 12. Do not use 5 as the number of monthly periods for a five-year term.
• Rounding every intermediate value: Keep full precision in formulas and format cells to show two decimal places. Rounding the balance or monthly interest at each calculation can create cumulative discrepancies.
• Assuming every loan uses monthly fixed payments: This method models equal monthly payments with a fixed periodic rate. Variable rates, irregular payment dates, interest-only periods, balloon payments, and extra payments need different formulas or additional rows and inputs.
• Expecting the last balance to be exactly zero: Cent rounding can leave a residual. Reconcile the final payment and balance rather than interpreting a few cents as a formula failure.

The standard calculation also assumes the stated APR can be converted directly to a monthly rate and that payments are made on schedule. Actual contract terms may use a different interest calculation or include other amounts. The calculator does not include fees, taxes, insurance, PMI, or escrow.

Assumptions

• Interest rate remains fixed for the full modeled term.
• Payments are equal, monthly, and made at the end of each period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI, escrow, and payment-date variations are not included.

Frequently Asked Questions

Can Excel make an amortization schedule automatically?
Yes. Excel’s PMT function can calculate a level payment, while formulas for interest, principal, and ending balance create each schedule row. Fill the row formulas down for the full payment count.

What is the Excel formula for monthly loan payment?
For a positive loan amount in B1, APR as a percentage in B2, and term in years in B3, use =-PMT(B2/1200,B3*12,B1). To display cents, wrap it in ROUND with 2 decimal places.

Why does the last balance in my Excel schedule not equal zero?
Most often, the regular payment was rounded to cents or intermediate amounts were rounded. Retain full precision in the calculations, and adjust the last payment to cover the remaining balance and final period’s interest.

Does an amortization schedule include taxes and insurance?
Not in this calculation. It models principal and interest only; taxes, insurance, PMI, escrow, and fees must be added separately if you need to estimate a broader periodic payment.

Can I use the schedule for a variable-rate loan?
Not unchanged. This schedule assumes a fixed rate. A variable rate requires updating the rate and recalculating payments or modeling the contract’s rate-reset and payment rules.

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

Related

• Low Interest Rate? Calculate a Loan Payment
• Amortization Schedule With Extra Payments XLS
• Amortization Schedule With Balloon Payment 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