Amortization Schedule XLS: Formula and Example

An amortization schedule XLS is a spreadsheet that shows how each loan payment is divided between interest and principal, along with the remaining balance. Enter the loan amount, annual percentage rate (APR), and term in this calculator to estimate the fixed monthly payment and total interest; the schedule details and formula below explain how to build or check the spreadsheet.

Formula

For a fixed-rate loan with equal monthly payments, the payment is calculated using the standard level-payment formula:

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

• P is the starting loan principal, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100. In the calculator, that is apr / 1200.
• n is the number of monthly payments: loan term in years × 12.
• Payment is the regular monthly amount, in dollars. The calculator rounds it to the nearest cent.

For each schedule row, interest is the opening balance multiplied by the monthly rate. Principal paid is the payment minus that period’s interest. The closing balance is the opening balance minus principal paid. Total interest in this calculator is the rounded monthly payment multiplied by the number of payments, minus the original principal. This treats the displayed payment as the amount paid every month; real loan schedules can differ slightly when the final payment is adjusted to clear a small remaining balance.

Worked Example

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

1. Convert APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
2. Calculate 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 rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The calculator’s headline result is the estimated monthly payment, $405.53. Its second result is estimated total interest, $4,331.80, under the stated assumptions.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are equal and made monthly at the end of each period.
• Interest compounds monthly at APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and other charges are not included.

How an Amortization Schedule XLS Works

A spreadsheet schedule applies the same calculation repeatedly, once for each payment period. A typical XLS or XLSX worksheet has columns for the payment number or date, opening balance, payment, interest, principal, and closing balance. The first row starts with the original principal; each later row starts with the prior row’s closing balance.

For the example above, the first month’s interest is $20,000 × 0.0066667, or about $133.33. Subtracting that from the $405.53 payment leaves about $272.20 of principal, so the estimated balance after the first payment is $19,727.80. In later months, interest is calculated on a smaller balance, so the interest portion generally falls and the principal portion rises while the scheduled payment stays level.

In a spreadsheet, a row can use formulas equivalent to opening balance × monthly rate for interest, payment − interest for principal, and opening balance − principal for closing balance. Copy the formulas down for the full number of payments, and check that the last balance is zero or close to zero. Spreadsheet functions and cell references differ by layout, so confirm that formulas point to the intended input and previous-row cells.

When to Use the Estimate and How to Read It

This estimate is useful for understanding the principal-and-interest pattern of a fixed-rate loan, checking a spreadsheet’s basic arithmetic, or comparing calculated figures with a lender’s disclosed payment schedule. It does not tell you whether a loan is suitable, what a lender will approve, or what the complete amount due will be. Use the actual principal, APR, and term supplied for the loan rather than assuming that the example inputs apply.

Interest and payment results depend on the assumptions. A changing rate, irregular payment dates, a first payment made on a different schedule, or extra payments can alter the balance path. The displayed total interest is a mathematical estimate based on the rounded monthly payment, not a substitute for the lender’s itemized schedule.

Common Spreadsheet Problems and Limits

• Entering 8 instead of 0.08: The calculator expects APR as a percentage, so enter 8 for 8%—not 0.08 and not 8 as a decimal in a separate spreadsheet formula unless that formula divides by 100.
• Using the wrong term unit: The calculator accepts years and converts them to months. A spreadsheet formula expecting months should use 60 for five years, not 5.
• Rounding each component too early: Rounding monthly interest and principal to cents can create a small residual balance. Keep full precision in intermediate spreadsheet calculations where possible, display currency to two decimals, and let the final payment clear any remaining amount.
• Assuming every payment is entirely principal and interest: Taxes, insurance, fees, and escrow may affect a real bill but are outside this calculation.

A fixed-payment amortization formula is not appropriate without changes for a variable rate, interest-only period, balloon payment, skipped payment, or a payment pattern that changes over time. The term input is limited to 0.0833–50 years, APR to 0.01%–100%, and principal to $1–$100,000,000; these are calculator boundaries, not statements about available loan terms or lender limits.

Frequently Asked Questions

What does XLS mean in amortization schedule XLS?
XLS is the file extension used by older Microsoft Excel workbooks. Newer Excel workbooks commonly use XLSX. The schedule itself is a table of payment-by-payment interest, principal, and balance figures; its calculations do not depend on the file extension.

How do I calculate interest for each month?
Multiply the opening balance for that month by the monthly rate, calculated here as APR ÷ 1,200. Subtract that interest from the scheduled payment to estimate principal paid in that period.

Why does my spreadsheet’s final balance not equal zero?
Small differences can result from rounding, using a different payment date convention, or rounding interest and principal in every row. Check the APR conversion, term in months, payment formula, and whether the last payment is adjusted to clear the balance.

Does an amortization schedule include taxes and insurance?
Not in this calculator’s principal-and-interest estimate. A lender’s payment or account statement may include additional items, so review its breakdown for charges not represented by the schedule.

Can I use this for a variable-rate loan?
Not as a complete schedule. This calculator assumes one fixed APR throughout the term; a rate change requires recalculating payments or modeling each rate period separately.

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

Related

• Amortization Schedule Creator: Calculate Loan Payments
• Amortization Schedule Calculator With Balloon Payment
• Car Loan 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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2