You can create a loan interest schedule in Excel by using the built-in PMT, IPMT, and PPMT functions, or by building a simple amortization table from scratch with a few formulas. This page provides a calculator that generates the monthly payment and total interest for a fixed-rate loan, and explains exactly how to replicate that schedule in your own spreadsheet.
Formula
The standard fixed-rate loan payment formula is:
PMT = P × (r / (1 − (1 + r)^(−n)))
- P = principal (loan amount), in dollars
- r = monthly interest rate = annual rate / 12 (as a decimal, so 8% becomes 0.08/12 = 0.0066667)
- n = total number of monthly payments = term in years × 12
For each payment period, the interest portion is IPMT = current balance × r, and the principal portion is PPMT = PMT − IPMT. After each payment, the new balance is the previous balance minus the principal portion.
Worked Example
Using the calculator defaults: loan amount $20,000, APR 8%, term 5 years.
- Monthly rate r = 8 / 1200 = 0.0066667
- Total payments n = 5 × 12 = 60
- PMT = 20000 × (0.0066667 / (1 − (1.0066667)^(−60)))
- Calculate (1.0066667)^(−60) ≈ 0.67121
- 1 − 0.67121 = 0.32879
- 0.0066667 / 0.32879 = 0.020276
- 20000 × 0.020276 = 405.52
The monthly payment is $405.52. Total paid over 60 months = 405.52 × 60 = $24,331.20. Total interest = $24,331.20 − $20,000 = $4,331.20, matching the calculator output.
Assumptions
- Fixed interest rate for the entire term.
- Payments are made at the end of each month (ordinary annuity).
- Interest compounds monthly at APR/12.
- No fees, taxes, insurance, or prepayment penalties are included.
How to Build the Schedule in Excel
Excel has three functions that do the heavy lifting. In a new sheet, set up columns: Period, Payment, Interest, Principal, Balance.
- Enter loan amount in B1, annual rate in B2, term in years in B3.
- In B4, type
=PMT(B2/12, B3*12, -B1)to get the monthly payment (negative because it’s an outflow). - For period 1, interest =
=B1*(B2/12). - Principal =
=B4 - interest. - New balance =
=B1 - principal. - Copy the formulas down for each period, always referencing the previous balance.
Alternatively, use =IPMT(B2/12, period, B3*12, -B1) and =PPMT(B2/12, period, B3*12, -B1) directly, where ‘period’ is the row number.
Common Mistakes and Misconceptions
A frequent error is using the annual rate directly in the PMT function without dividing by 12. Another is forgetting that the payment is rounded to cents – using the unrounded payment can cause a small discrepancy in the final balance. Also, many people assume that interest is calculated on the original principal each month, but in a standard amortization, interest is charged on the remaining balance, so the interest portion decreases over time.
When to Use a Custom Spreadsheet vs. a Calculator
A pre-built online calculator is fastest for a single estimate. A custom Excel schedule is useful when you want to:
- See the exact breakdown for each month
- Add extra payments and see how they shorten the term
- Compare different scenarios side by side
- Export the schedule for record-keeping or analysis
For most one-off questions, the calculator above gives the same result as a full spreadsheet, but the spreadsheet gives you control.
Frequently Asked Questions
How do I add extra payments to the schedule?
In your Excel table, add a column for extra payment. Each month, subtract the extra from the balance before calculating the next period’s interest. You’ll also need to adjust the final payment, which will be smaller than the regular payment. The total interest will decrease because you’re paying down principal faster.
Why does my Excel PMT result differ from the calculator?
Check that you’re using the same inputs and that the rate is divided by 12. Also, Excel’s PMT returns a negative value by default; if you see a negative number, it’s correct, just formatted differently. Rounding to two decimal places can also cause a few cents difference.
Can I use this for a variable-rate loan?
No, the formula assumes a fixed rate. For variable rates, you’d need to recalculate the payment whenever the rate changes, which is more complex. The schedule would have segments with different payments.
What is the difference between APR and interest rate?
APR includes the interest rate plus certain fees and costs, expressed as a yearly rate. For a simple loan with no fees, APR equals the interest rate. When building a schedule, use the APR if it reflects your actual cost, but note that fees are not included in the payment formula.
How do I find the total interest paid?
Sum the interest column in your schedule, or use the formula: total interest = (monthly payment × number of payments) − loan amount. The calculator above shows this as ‘Total interest’.
Is it better to pay bi-weekly instead of monthly?
Bi-weekly payments effectively make one extra monthly payment per year, which reduces the balance faster and lowers total interest. You can model this in Excel by using a payment frequency of 26 per year, but the standard formula here is for monthly payments.
This calculator provides an estimate for educational purposes; your lender’s amortization statement will show the exact figures.
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
- Debt Ratio Benchmarks: What Is a Good Debt Ratio?24.0 %
- Debt Ratio Calculation Formula & How to Use It25.00 %
- Car Loan Calculator: Estimate Your Monthly Payment$405.53
- Calculate My Payoff Date: Loan Payoff Calculator
- Student Loan Interest Deduction Limit: 2025 Rules$405.53
- Car Loan EMI Calculator Month Wise$405.53