Amortization Schedule Spreadsheet: Payment and Interest

An amortization schedule spreadsheet shows how each 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 itself can then be built from those figures.

Formula

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

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

• P is the amount borrowed, in dollars.
• r is the monthly interest rate, calculated as annual APR ÷ 1,200. For 8% APR, r = 0.08 ÷ 12, or about 0.006667 per month.
• n is the number of monthly payments: loan term in years × 12.

The spreadsheet calculator rounds the payment to cents. Total interest is the rounded monthly payment multiplied by the number of payments, minus the original principal. Because monthly payments are rounded, an actual lender’s final payment or disclosed total may differ slightly.

Worked Example

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

1. Convert the APR to a monthly rate: 8 ÷ 1,200 = 0.0066667.
2. Find 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.

Assumptions

• The interest rate stays fixed throughout the term.
• Payments are monthly and made at the end of each payment period.
• Interest compounds monthly at APR ÷ 12.
• Fees, taxes, insurance, PMI, and escrow are not included.

How an Amortization Schedule Works

A spreadsheet schedule expands the payment calculation into one row per payment period. At the start of a row, it uses the current outstanding balance. Interest for that period is the balance multiplied by the monthly rate; the principal portion is the payment minus that interest. Subtracting principal paid from the starting balance gives the next period’s balance.

For the example, first-month interest is $20,000 × 0.0066667, or about $133.33. Of the $405.53 payment, roughly $272.20 goes to principal, leaving an estimated balance of $19,727.80. In later months, the balance is smaller, so the interest portion generally falls and more of each level payment goes to principal. The balance should reach approximately zero after the final scheduled payment, subject to rounding.

A typical schedule includes payment number or date, starting balance, payment, interest, principal, and ending balance. A spreadsheet can also total the interest and principal columns. Dates are useful for tracking real payment timing, but the simple monthly formula assumes evenly spaced periods and does not calculate interest from actual calendar days.

Building or Checking a Spreadsheet

To build a schedule, first calculate the fixed payment, then make one row for each month. For each row, calculate interest from the opening balance, calculate principal as payment minus interest, and calculate closing balance as opening balance minus principal. The next row’s opening balance equals the previous row’s closing balance.

• Keep the unrounded formulas in the spreadsheet where practical, while displaying money to two decimal places.
• If the modeled payments are rounded to cents, check the last row. Adjusting the final payment by a small rounding amount may be necessary to bring the balance to zero.
• Use the contractual payment frequency and rate convention. A monthly schedule does not directly model weekly, biweekly, or irregular payments.
• Compare the estimated payment and total cost with the lender’s disclosure, which uses the loan’s actual terms and timing.

What the Schedule Includes—and Misses

A basic amortization schedule describes principal and interest on a fixed-rate loan with equal periodic payments. It does not automatically show the complete amount due on a mortgage or other loan if separate charges are collected. Taxes, insurance, escrow deposits, fees, and other items need their own inputs and calculations.

It also does not model a changing rate, skipped or late payments, payment holidays, fees added to the balance, or extra principal payments. Those events change the balance path and may affect the payoff date or total interest. An adjustable-rate loan needs separate rate periods and a recalculation when its rate changes. For an irregular schedule, the lender’s accrual method and payment dates matter.

A common misconception is that every dollar of a level payment reduces principal. Early in a fully amortizing loan, part of the payment covers interest accrued on the outstanding balance; only the remainder reduces principal. The schedule makes this division visible, but does not determine whether a loan meets anyone’s needs or what a lender will charge.

Frequently Asked Questions

What is an amortization schedule spreadsheet?
It is a table that lays out each scheduled payment and shows the interest, principal, and remaining balance for each period.

How do I calculate the monthly payment in a spreadsheet?
Use the level-payment formula with the principal, monthly rate (APR divided by 1,200), and total number of monthly payments. The calculator above applies it and rounds the result to cents.

Why does the interest portion decrease over time?
For a fixed-rate loan, each period’s interest is based on the outstanding balance. As principal is repaid, the balance falls, so less interest accrues in later periods.

Why does my spreadsheet differ from my loan statement?
Possible causes include rounding, payment dates, a different interest-accrual convention, fees, rate changes, or extra and missed payments. The simple estimate assumes equal monthly periods and excludes separate charges.

Can I use this schedule for extra payments?
Not without changing the schedule formulas. An extra principal payment reduces the balance beyond the regular principal portion, so subsequent interest and possibly the payoff date must be recalculated.

Does total interest include fees or insurance?
No. The displayed total is estimated interest only, based on the entered principal, APR, and term; it excludes fees, insurance, taxes, and escrow.

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

Related

• Interest Rate Models: How They Work and What They Show
• Interest Rate Movements: How They Affect Loan Payments
• Interest Rate Meeting Calculator: Estimate 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