Yes, you can get a free amortization schedule calculator for Excel, and this page gives you one that runs right here in your browser—enter the loan amount, interest rate, and term to see your monthly payment and total interest. You can also build the same schedule yourself with a simple spreadsheet formula, which we explain step by step below. The calculator and instructions work for any fixed-rate loan: mortgages, auto loans, personal loans, and student loans.
Formula
The calculator uses the standard fixed-rate loan payment formula:
Monthly Payment = P × (r × (1 + r)^n) / ((1 + r)^n − 1)
Where:
- P = loan principal (the amount you borrow, in dollars)
- r = monthly interest rate = APR / 12, expressed as a decimal (e.g., 8% APR → 0.08/12 = 0.0066667)
- n = total number of monthly payments = loan term in years × 12
The formula assumes a fixed interest rate for the entire term and that payments are made at the end of each month. The total interest is simply the monthly payment times the number of payments, minus the 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: r = 8 / 1200 = 0.0066667
- Total payments: n = 5 × 12 = 60
- Compute (1 + r)^n = (1.0066667)^60 ≈ 1.4898457
- Payment = 20000 × (0.0066667 × 1.4898457) / (1.4898457 − 1) = 20000 × (0.0099323) / (0.4898457) ≈ 20000 × 0.020276 = 405.52
- Total paid = 405.52 × 60 = 24,331.20
- Total interest = 24,331.20 − 20,000 = 4,331.20
The calculator shows a monthly payment of $405.52 and total interest of $4,331.20, matching the manual calculation.
Assumptions
- Fixed interest rate for the whole loan term.
- Payments are made at the end of each month.
- Interest compounds monthly at APR / 12.
- No fees, taxes, insurance, PMI, or escrow are included—those would change the actual payment.
- The result is an estimate; the lender’s disclosure gives the exact figures.
How to Build an Amortization Schedule in Excel
If you prefer to see the full schedule—each payment’s split between interest and principal, and the remaining balance—you can create it in Excel in a few minutes. Here’s how:
- Enter your inputs in cells: A1 = loan amount, A2 = annual interest rate (as a decimal, e.g., 0.08), A3 = number of years.
- Set up columns: Payment #, Payment Amount, Interest Paid, Principal Paid, Remaining Balance.
- In the first row (Payment 1), calculate interest = previous balance × (annual rate / 12). For the first payment, previous balance is the loan amount.
- Principal paid = monthly payment − interest paid.
- New balance = previous balance − principal paid.
- Copy the formulas down for each payment period (e.g., 60 rows for a 5-year loan).
Excel’s built-in functions can also help: PMT(rate, nper, pv) gives the monthly payment, and IPMT and PPMT give the interest and principal portions for a specific period. For example, =PMT(0.08/12, 60, 20000) returns −405.52 (negative because it’s a payment).
Why Use an Amortization Schedule?
An amortization schedule shows you exactly how each payment is applied. In the early years, most of your payment goes toward interest; later, most goes toward principal. For example, with the $20,000 loan above, the first payment’s interest is $133.33 and principal is $272.19. By the last payment, interest is just $2.69 and principal is $402.83.
Seeing this breakdown helps you understand how much interest you’ll pay over the life of the loan and how extra payments can shorten the term. If you make an extra payment of $100 in the first month, you’ll reduce the balance faster and save interest—but the exact savings depend on your loan terms.
Common Mistakes and How to Avoid Them
- Using the annual rate instead of monthly: Always divide the APR by 12 for monthly payments. Using 8% instead of 0.6667% will give a wildly wrong payment.
- Forgetting to convert years to months: A 5-year loan has 60 months, not 5. The formula requires the total number of payments.
- Rounding too early: Keep full precision in your spreadsheet or calculator until the final step. Rounding the monthly rate to 0.0067 instead of 0.0066667 can change the payment by a few cents.
- Ignoring fees and insurance: The formula only covers principal and interest. Real payments often include property tax, insurance, or PMI, which you must add separately.
- Assuming the rate stays fixed: If you have an adjustable-rate mortgage, the payment will change when the rate changes. This calculator is only for fixed-rate loans.
When to Use This Calculator vs. a Full Schedule
Use the simple calculator when you just need a quick estimate of your monthly payment and total interest. Use a full amortization schedule when you want to see the balance at any point, plan extra payments, or compare loan offers with different terms. You can also use the schedule to figure out how much you’d still owe after a certain number of years—useful if you plan to sell or refinance.
For extra payments, you can modify the schedule by adding an extra principal column and recalculating the balance. Many free Excel templates already include this feature, but you can also build it yourself by adding a lump-sum payment row.
Frequently Asked Questions
Can I use this calculator for a mortgage?
Yes, the formula works for any fixed-rate loan. For a mortgage, enter the loan amount (not the home price), the annual interest rate, and the term in years. Remember that your actual mortgage payment will also include property taxes, homeowners insurance, and possibly PMI, which are not included here.
How do I account for extra payments?
To see the effect of extra payments, you need a full amortization schedule, not just the basic payment formula. Add a column for extra principal and subtract it from the balance each month. The loan will be paid off early, and the total interest will be lower. You can use the schedule to find out how much time and money you save.
What’s the difference between APR and interest rate?
The interest rate is what you pay on the principal. APR (Annual Percentage Rate) includes the interest rate plus certain fees and costs, expressed as a yearly rate. For this calculator, use the APR if it’s the only rate you have, but know that the actual payment may differ slightly if fees are financed separately.
Why does the monthly payment show as negative in Excel’s PMT function?
Excel’s PMT function returns a negative number because it represents money you pay out. To see a positive number, wrap the formula in =-PMT(...) or use =ABS(PMT(...)). The absolute value is the monthly payment amount.
Can I make a schedule for a variable-rate loan?
No, this calculator and the standard formula assume a fixed rate. For an adjustable-rate mortgage, you’d need to recalculate each time the rate changes, using the new rate for the remaining balance and term. That’s more complex and usually requires specialized software.
The result is an estimate and the lender’s disclosure gives 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
- 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
- Houston Metro Q Card Balance: Check & Add Funds$35.00