Loan Amortization Spreadsheet: Payments and Balance

A loan amortization spreadsheet lays out each scheduled payment, separating principal from interest and tracking the remaining balance over time. Enter the loan amount, annual interest rate, and term to estimate the monthly payment, total interest, and month-by-month payoff schedule.

Formula

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

M = round(P × r ÷ [1 − (1 + r)−n], 2)

• P is the starting loan principal, in dollars.
• r is the monthly interest rate, found by dividing APR in percent by 1,200.
• n is the total number of monthly payments, found by multiplying the term in years by 12.
• M is the monthly payment, in dollars, rounded to the nearest cent.

Total interest over the scheduled term is estimated as M × n − P. The spreadsheet’s schedule then applies each payment to interest due and principal. For a standard monthly schedule, interest for a period is the opening balance multiplied by r; principal paid is the payment minus that interest; and the closing balance is the opening balance minus principal paid.

Worked Example

Using the defaults above: principal P = $20,000, APR = 8%, and term = 5 years.

1. Convert the annual rate to a monthly decimal: r = 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: n = 5 × 12 = 60.
3. Substitute into the payment formula: M = round[$20,000 × 0.0066667 ÷ (1 − (1.0066667)⁻⁶⁰), 2] = $405.53 per month.
4. Use the rounded monthly payment to estimate total interest: $405.53 × 60 − $20,000 = $4,331.80.

These results describe the scheduled payments under the stated assumptions. A lender’s schedule may differ if the actual rate, dates, fees, payment rules, or rounding differ.

Assumptions

• The interest rate stays fixed for the full term.
• Payments are monthly, made at the end of each period, with the first payment one month after the loan starts.
• Interest accrues monthly at APR divided by 12, using the APR as a nominal annual rate.
• Fees, taxes, insurance, PMI, escrow, and any other charges are not included.

How an Amortization Spreadsheet Works

A full spreadsheet typically has one row for the starting balance and one row for each monthly payment. Each payment is divided according to the interest accrued during that period. Early in a fully amortizing loan, the balance is relatively large, so more of the level payment generally goes toward interest. As principal falls, interest due falls too, and a larger share of the payment goes toward principal. With the scheduled payments and assumptions held constant, the balance reaches zero at the end of the term, subject to rounding.

Useful columns in a schedule include payment number or date, opening balance, payment, interest, principal, and closing balance. For example, in the first month of this example, interest is approximately $20,000 × 0.0066667 = $133.33. Subtracting that from the $405.53 payment leaves about $272.20 to reduce principal, bringing the balance to approximately $19,727.80. Small differences can appear in later rows if calculations retain extra decimal places internally or round each interest amount to cents.

Building and Checking a Schedule

In spreadsheet software, place the loan amount, APR, term, and calculated payment in clearly labelled cells. Set the first opening balance equal to principal. For each monthly row, calculate interest from the opening balance, principal as payment minus interest, and closing balance as opening balance minus principal. Carry the closing balance into the next row as its opening balance. Use 60 payment rows for a five-year monthly term.

Check the schedule rather than relying only on the payment formula:

• At the start, opening balance should equal the original principal.
• For each row, payment should equal interest plus principal, apart from display rounding.
• The closing balance should decline when the payment exceeds the period’s interest.
• After the final scheduled payment, the balance should be zero or close to zero; a small residual can result from rounding.

If a spreadsheet rounds every row’s interest and principal to cents, the last payment may need a small adjustment to clear a residual balance. Do not silently treat that adjustment as a lender’s actual payment; use the lender’s statement or payoff quote for real account figures.

When to Use It and Common Errors

An amortization spreadsheet can help explain how a fixed-payment loan changes over time, estimate the remaining balance after a chosen number of payments, or compare arithmetic scenarios by changing one input at a time. It is an estimate, not a loan offer or an account ledger. Enter the rate and term shown in the lender’s documents, and confirm whether the displayed rate is an APR or a different rate measure before using it.

• Mixing annual and monthly units: APR must be divided by 1,200 for a monthly decimal rate, not by 12 alone. The term in years must be multiplied by 12 to get monthly payments.
• Confusing APR with the periodic rate: Enter 8 for 8% APR, not 0.08; the formula converts percent to a monthly rate.
• Assuming every payment is identical in practice: A variable rate, irregular payment dates, fees added to the balance, or payment changes can make a real schedule differ.
• Rounding too early: Rounding the monthly payment to cents is necessary for the displayed estimate, but rounding intermediate values can create small differences in the total and final balance.
• Reading total interest as the full cost of borrowing: This calculation excludes charges and other costs that are not part of the principal-and-interest formula.

Extra payments are not included in this calculator. In a separate schedule, an extra principal payment usually changes later balances and may shorten the payoff period, but actual payment allocation and any applicable terms must be checked with the lender. This tool cannot determine a payoff amount for a particular date because that can depend on exact dates, accrued interest, and account-specific details.

Frequently Asked Questions

What is a loan amortization spreadsheet?
A table that estimates each scheduled payment’s interest and principal portions and shows how the loan balance changes over time.

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

Why does more of an early payment go to interest?
Interest for the period is calculated on the opening balance. Since the balance is largest at the beginning, the interest portion is generally larger then; as principal is repaid, that portion declines.

Does the calculator include extra payments or fees?
No. It assumes fixed monthly payments and excludes extra payments, fees, taxes, insurance, PMI, and escrow.

Why might my spreadsheet not match my lender’s schedule?
Possible reasons include a different rate or term, payment dates, daily rather than monthly interest calculations, fees, variable rates, or rounding conventions. Check the loan documents and ask the lender for account-specific figures.

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

Related

• Loan Term Sheet: Meaning, Terms, and How to Review
• Loan Term Out Calculator: Estimate Months to Payoff
• Loan Term Options and Monthly 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 | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2