Loan Amortization XLS: Calculate Payments and Balances

A loan amortization XLS is a spreadsheet that lays out each scheduled payment, showing how much goes to interest and how much reduces the loan balance. Use the calculator below to estimate a fixed-rate monthly payment and total interest from a loan amount, annual APR, and term; it returns both figures for a standard monthly amortization schedule.

Formula

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

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

• P is the starting principal, or loan amount, in dollars.
• r is the monthly interest rate as a decimal: annual APR ÷ 1,200.
• n is the number of monthly payments: loan term in years × 12.
• Payment is the monthly amount in dollars, rounded to cents.

Total interest is estimated as monthly payment × n − P, in dollars. Because it uses the rounded monthly payment, the total reflects payment rounding across the schedule. A spreadsheet schedule can calculate each period separately: interest for a month 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 calculator’s default values—$20,000 principal, 8% annual APR, and a five-year term:

1. Convert APR to a monthly decimal rate: 8 ÷ 1,200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Substitute into the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = $405.53, rounded to cents.
4. Calculate total interest using that rounded payment: $405.53 × 60 − $20,000 = $4,331.80.

The estimated monthly payment is $405.53, and estimated total interest is $4,331.80. An actual lender schedule may differ if its payment dates, rounding method, fees, or rate structure differ.

Assumptions

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

What an XLS Amortization Schedule Shows

“XLS” commonly refers to Microsoft Excel’s older workbook format; newer Excel workbooks often use XLSX. Either format can hold an amortization table, and the underlying method is the same. A typical table has one row per payment period and columns for the payment number or date, opening balance, scheduled payment, interest, principal, and closing balance.

At the start of a fully amortizing fixed-rate loan, the balance is largest, so the interest portion of a level payment is also relatively large. As principal is paid down, the interest portion generally shrinks and the principal portion grows. The payment amount can stay level even though its components change. The final row should bring the balance to zero, subject to rounding adjustments.

For example, on the first month of the worked example, interest is approximately $20,000 × 0.0066667, or $133.33. Subtracting that from the $405.53 payment leaves about $272.20 for principal. The balance then falls to about $19,727.80. The next month’s interest is calculated on that lower balance. A spreadsheet may show a small final-payment adjustment because it rounds amounts to cents each period.

How to Read and Use the Estimate

The calculator gives a payment and overall interest estimate, not a complete downloadable XLS file. To build a spreadsheet, enter the initial balance in the first row, calculate monthly interest from the current balance, subtract interest from the payment to get principal, and carry the resulting balance into the next row. Repeat for the number of scheduled payments.

Some spreadsheet applications include financial functions such as PMT for the payment calculation. The function’s rate argument must be the monthly rate, not the annual percentage, and its period argument must use the same unit as that rate. A formula using annual APR with 60 monthly periods would mix units and produce a wrong result. Check how the spreadsheet represents cash inflows and outflows: some financial functions display a payment as negative because it is a cash outflow.

Use the lender’s stated principal, APR, and term as inputs when available. APR and the interest rate used to accrue interest are not always interchangeable in every loan product; this calculator treats the entered APR as the nominal annual rate divided by 12. It does not separately model an origination charge or other financed fee. If such an amount is added to the balance, the principal input should reflect the amount actually financed, while the lender’s disclosure remains the source for the contractual schedule.

Common Spreadsheet Problems and Limits

• Wrong rate conversion: For a nominal APR entered as a percentage, divide by 1,200 to get the monthly decimal rate. Dividing by 12 alone leaves a percentage rather than a decimal.
• Wrong term units: A five-year term means 60 monthly periods, not five. Keep the payment frequency and number of periods consistent.
• Rounding too early: Round the displayed payment to cents, but understand that a lender may calculate interest and adjust the last payment according to its own rules. Repeated rounding can leave a small residual balance.
• Assuming every loan is fixed and fully amortizing: Variable-rate loans, interest-only periods, balloon payments, irregular payment dates, and additional payments require different calculations.
• Leaving out costs: The payment here excludes charges and non-loan items. A payment shown in a loan disclosure or bill may include amounts this calculation does not cover.

A level-payment formula also assumes the full scheduled payment is made on time each month and that the loan does not change during its term. It does not calculate a payoff quote for a particular date, since daily interest, payment posting, and fees may matter. If a payment is smaller than the interest accruing for a period, the balance may not decline as a standard amortization table assumes.

Frequently Asked Questions

Can I download a loan amortization XLS file here?
This page calculates the standard payment and total interest but does not attach a spreadsheet file. You can use the formula and schedule columns described above to create a workbook in a spreadsheet application.

How do I calculate a monthly loan payment in Excel?
Use a level-payment formula with the monthly rate and total number of monthly periods. For the defaults here, those inputs are 8% ÷ 12 as a percentage rate and 60 periods; use the spreadsheet’s function guidance to handle cash-flow signs consistently.

Does an amortization schedule include interest?
Yes. A standard schedule separates each payment into interest and principal, then shows the remaining balance. This calculator also estimates total interest over the full term under its stated assumptions.

Why does my spreadsheet’s final balance not equal zero?
Small differences can result from rounding every row, using a different payment date convention, or entering a rate or term in inconsistent units. Lender schedules may adjust the final payment to clear the remaining balance.

Does this estimate include extra payments?
No. It assumes the same scheduled payment each month and does not model additional principal payments. Extra payments change the balance path and can affect total interest and payoff timing.

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

Related

• Loan Term Out Calculator: Estimate Months to Payoff
• Loan Term Options and Monthly Payment Calculator
• Loan Term Out? Calculate Monthly Payments and Interest

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