Yes, you can create a free amortization table in Excel without any templates or add-ins, using just the built-in PMT function and a few formulas. You enter the loan amount, interest rate, and term, and the table shows each monthly payment split into interest and principal, plus the running loan balance.
Formula
The standard amortization formula calculates the fixed monthly payment (PMT) that repays a loan over a set number of periods at a fixed interest rate:
PMT = P × (r × (1 + r)^n) / ((1 + r)^n − 1)
Where:
- P = principal loan amount (in dollars)
- r = monthly interest rate = annual percentage rate (APR) ÷ 12 ÷ 100 (as a decimal)
- n = total number of monthly payments = loan term in years × 12
Once you have the monthly payment, each row of the amortization table uses two more formulas:
- Interest portion = previous balance × r
- Principal portion = monthly payment − interest portion
- New balance = previous balance − principal portion
The total interest paid over the life of the loan is simply the monthly payment × number of payments − principal.
Worked Example
Using the default values from the calculator above — a $20,000 loan at 8% APR for 5 years:
- Convert APR to monthly rate: 8 ÷ 12 = 0.6667% per month, or 0.006667 as a decimal.
- Total payments: 5 × 12 = 60.
- Apply the formula: PMT = 20000 × (0.006667 × (1.006667)^60) / ((1.006667)^60 − 1).
- (1.006667)^60 ≈ 1.48985.
- Numerator: 20000 × (0.006667 × 1.48985) ≈ 20000 × 0.0099323 ≈ 198.65.
- Denominator: 1.48985 − 1 = 0.48985.
- PMT ≈ 198.65 / 0.48985 ≈ 405.53 (rounded to the cent).
The calculator above gives $405.53 for the monthly payment. Total paid over 60 months = 60 × 405.53 = $24,331.80, so total interest = $24,331.80 − $20,000 = $4,331.80, matching the calculator’s output.
Assumptions
- The interest rate is fixed for the entire term.
- Payments are made at the end of each month (ordinary annuity).
- Interest compounds monthly at APR ÷ 12.
- The table does not include fees, taxes, insurance, PMI, or escrow amounts.
How to Build the Table in Excel
Creating the table takes about five minutes once you have the payment amount. Here are the steps:
- In cell A1 enter the loan amount, in B1 the annual interest rate (as a percentage), in C1 the term in years.
- In D1 enter the formula:
=ROUND(PMT(B1/12/100, C1*12, -A1), 2). The negative sign makes the payment a positive number. - Create column headers: Period, Payment, Interest, Principal, Balance.
- In the first row (period 1), set Interest = previous balance × monthly rate, Principal = payment − interest, Balance = previous balance − principal.
- Copy the row down for all periods. The final balance should be $0.00 (or within a cent due to rounding).
- Sum the Interest column to get total interest paid.
If you prefer not to type formulas, many free Excel templates for amortization schedules exist online, but building your own ensures you understand exactly what each number represents.
Common Mistakes and How to Avoid Them
Several errors are common when creating an amortization table:
- Using the annual rate instead of the monthly rate. Always divide the APR by 12 before using it in the interest formula. For 8% APR, the monthly rate is 0.08/12 = 0.006667.
- Forgetting to convert years to months. A 5-year loan has 60 payments, not 5.
- Rounding the payment too early. If you round the payment to $405.53 and then use that rounded number in every row, the final balance may be off by a few cents. That is normal; the last payment can be adjusted to bring the balance to zero.
- Using the wrong sign for the principal in the PMT function. In Excel,
=PMT(rate, nper, pv)returns a negative number if the present value is positive. Use-A1as the present value to get a positive payment.
When to Use an Amortization Table
An amortization table is useful whenever you want to see the breakdown of a fixed-rate loan over time. Common uses include:
- Understanding how much of your early payments go toward interest versus principal (for a typical loan, interest is highest at the start).
- Comparing different loan terms — a 15-year mortgage has a higher payment but far less total interest than a 30-year mortgage.
- Planning extra payments — you can add an extra principal payment column to see how much time and interest you save.
- Preparing for tax or accounting records where interest amounts matter.
The table works for any fixed-rate loan: auto loans, personal loans, student loans, and mortgages. It does not apply to variable-rate loans or loans with interest-only periods, balloon payments, or other irregular structures.
Limitations of the Standard Table
The simple amortization table assumes every payment is exactly the same amount and is made on time. Real life differs in several ways:
- Late or early payments change the interest calculation for that period.
- Extra payments reduce the balance faster and shorten the term, but you must recalculate the table if you make them.
- Daily interest accrual — some loans calculate interest daily rather than monthly, which changes the numbers slightly.
- Fees and insurance are not part of the principal-and-interest breakdown.
For most budgeting and planning purposes, the standard table is accurate enough. For exact figures on a specific loan, always check the lender’s amortization schedule.
Frequently Asked Questions
Can I download a free amortization table Excel template? Yes, many websites offer free templates, and Microsoft Office also includes a basic loan amortization template. However, you can create the same table yourself in a few minutes with the PMT function and a simple formula for each row.
Why does my interest payment decrease each month? Because the principal balance decreases with each payment. The interest is calculated on the remaining balance, so as the balance shrinks, the interest portion shrinks too, leaving more of the fixed payment to go toward principal.
What happens if I make an extra payment? An extra payment goes entirely toward the principal (unless the lender applies it to interest first, which is rare). This reduces your balance, which means less interest accrues next month, and you may pay off the loan early. You can simulate this by adding an extra payment column to your table.
Is the PMT function the same as the formula shown here? Yes, Excel’s PMT function uses the same standard amortization formula. The advantage of writing it out manually is that you can see each component and modify it for extra payments or different compounding periods.
Why is my final balance not exactly zero? This happens because the monthly payment is rounded to the nearest cent, and interest is also rounded in each row. The difference is usually a few cents. To fix it, adjust the last payment so the balance ends at zero, or leave the rounding in place and accept the tiny discrepancy.
Can I use this for a loan with a different payment frequency, like bi-weekly? Yes, but you must adjust the formula. For bi-weekly payments (26 per year), divide the APR by 26 for the periodic rate and multiply the term in years by 26 for the number of periods. The table will then show each bi-weekly payment.
The result from any calculator or spreadsheet is an estimate based on the inputs you provide; the lender’s official amortization schedule will give the exact figures for your loan.
Related
Formula used
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
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- JS Bank Credit Card: Features & How to Apply$405.53
- JD Gift Card Balance: How to Check It$60.00
- JD Bank Credit Card: How It Works & What to Know$405.53
- In-N-Out Gift Card Balance: Check & Use Guide$30.00
- JCPenney Credit Card Balance: How to Check It27 months
- How to Check T Cash Card Balance