Free Loan Amortization Table Excel: Formula and Example

A free loan amortization table in Excel can show each scheduled payment split between principal and interest, along with the remaining balance. Enter the loan amount, annual interest rate, and term in the calculator below to get the fixed monthly payment and total interest; the sections that follow explain how to build and check a matching spreadsheet.

Formula

The calculator uses the standard level-payment formula for a fixed-rate loan with monthly payments:

• Monthly payment: M = P × r / [1 − (1 + r)⁻ⁿ]
• Monthly interest rate: r = APR / 1,200, converting an annual percentage rate into a decimal monthly rate.
• Number of payments: n = 12 × y, where y is the term in years.
• Total interest: I = M × n − P, using the monthly payment rounded to cents.

Here, P is the loan principal in dollars, APR is the annual rate as a percentage, y is the term in years, M is the monthly payment in dollars, and I is total interest in dollars. The monthly payment is displayed to two decimal places. As required for a cent-based estimate, total interest uses that rounded payment multiplied by the number of payments, then subtracts principal.

Worked Example

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

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

These totals assume 60 equal payments at the stated schedule. A lender’s actual final payment may differ because of rounding, payment dates, or contract terms.

Assumptions

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

What an Excel Amortization Table Shows

An amortization table lays out the loan period by period. A basic worksheet typically has columns for payment number or date, beginning balance, scheduled payment, interest, principal paid, and ending balance. The opening balance of a row becomes the next row’s beginning balance, so the schedule makes the path from the original principal to a zero balance visible.

For a fixed-rate loan with equal monthly payments, the payment is generally the same each month. The interest portion is calculated from the balance at the start of that period; the remainder of the payment reduces principal. Since the balance usually falls over time, the interest amount generally declines while the principal portion rises. This describes the formula’s model, not every loan contract.

Building the Table in Excel

Excel includes financial functions such as PMT, IPMT, and PPMT that can help create a schedule. Function names and argument order can vary in spreadsheet software, so check the help for the version you use. For a standard fixed-rate monthly loan, the rate supplied to a payment function should be the monthly rate, and the number of periods should be the total number of monthly payments.

1. Put the principal, annual APR, and term in separate input cells. Keep a clear label and unit beside each value.
2. Calculate monthly rate as APR divided by 12 if APR is stored as a decimal, or divided by 1,200 if it is entered as a whole percentage such as 8.
3. Calculate the number of payments as years multiplied by 12. Use the same period count consistently throughout the schedule.
4. For each row, calculate interest as beginning balance multiplied by the monthly rate. Calculate principal paid as the payment minus interest.
5. Calculate ending balance as beginning balance minus principal paid, then link that ending balance to the next row’s beginning balance.

Spreadsheet payment functions may display a negative value because they represent cash flowing out from the borrower’s perspective. Formatting or changing the sign can make the displayed payment positive, but do not accidentally reverse signs in only part of the schedule. Keep full precision in calculation cells and format displayed money to two decimals; rounding every intermediate cell can create a residual balance.

Checking the Schedule and Common Errors

A schedule is easier to trust when its totals reconcile. For a simple fixed-rate example, the sum of principal portions should be close to the original principal, and the ending balance after the final scheduled payment should be close to zero. Total interest should equal total payments minus principal under the schedule’s chosen rounding convention.

• Entering APR incorrectly: If the cell contains 8 for 8%, use 8/1,200 for a monthly decimal rate. If it contains 8%, use the spreadsheet’s stored decimal value, 0.08, divided by 12. Mixing these conventions makes the result wrong.
• Using years as payment periods: A five-year monthly loan has 60 periods, not five. Match the payment frequency to both the rate period and the total number of payments.
• Confusing payment with principal: The full payment is not applied to principal; the period’s interest is paid first in this model.
• Rounding each row too early: Display two decimal places without necessarily rounding the underlying formulas. If a contract requires cent-rounded installments, the final payment can need adjustment.
• Ignoring a changing rate or irregular dates: A fixed-rate monthly table does not reproduce variable-rate loans, skipped payments, daily-interest calculations, or fees unless those features are modeled separately.

When This Excel Table Is Useful—and Its Limits

A spreadsheet can help you understand how a stated principal, APR, and term translate into scheduled payments and a declining balance. It can also be used to explore hypothetical values, provided you label them as assumptions rather than lender-confirmed terms. Get the actual rate, payment schedule, fees, and other contract figures from the lender’s disclosure; they are not supplied by this calculator.

The calculator and a basic Excel table are estimates for level monthly payments. They do not determine approval, affordability, taxes, insurance, or the total cost of borrowing under terms not entered. Extra payments can reduce principal and may change the payoff timeline or interest, but a basic schedule does not include them unless you add an extra-payment input and specify how it is applied. A variable rate, payment timing other than month-end, or a different compounding convention also requires a different calculation.

Frequently Asked Questions

Is there a free loan amortization table in Excel?
Yes. You can build a table in a blank workbook using formulas or spreadsheet payment functions. The calculator on this page provides a free fixed-payment estimate; Excel itself does not need to be used to see the basic payment and interest result.

What information do I need to make an amortization schedule?
For a basic fixed-rate monthly schedule, you need the principal, annual interest rate, loan term, and payment frequency. A more precise schedule may also need payment dates, fees, rate changes, and the lender’s rounding rules.

Why does the interest portion fall each month?
In the fixed-rate model, each period’s interest is the opening balance multiplied by the monthly rate. As principal is repaid and the balance declines, the calculated interest generally declines too.

Does the monthly payment include taxes and insurance?
No. The payment shown here is principal and interest under the stated assumptions. Taxes, insurance, PMI, escrow, and fees are excluded unless separately included in a different calculation.

Why does my Excel result differ by a few cents?
Differences can come from rounding the payment or each row, using APR as 8 instead of 8%, counting periods incorrectly, or applying a different payment date or final-payment rule. Check the rate conversion and retain unrounded values in intermediate formulas.

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

Related

• Amortization Rate Table: Payments and Loan Balance
• Amortization Distribution Table: How to Read It
• 30 Year Amortization Table: Payment and Balance

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