calculate-loan

01 Loan payments

Free Amortization Calculator Excel: Build Your Own

Calculator No. 01-24197 · Updated · Formula and assumptions shown below

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

Yes, you can create a free amortization calculator in Excel yourself, and this page shows you exactly how to build one from scratch using standard formulas. You enter the loan amount, annual interest rate, and loan term, and the calculator returns the monthly payment, total interest, and a full amortization schedule. No paid templates or add-ins required.

Formula

The core of any amortization calculator is the monthly payment formula. For a fixed-rate loan with monthly compounding, the payment is:

M = P × [r(1 + r)^n] / [(1 + r)^n – 1]

Where:

  • M = monthly payment (in dollars)
  • P = principal loan amount (in dollars)
  • r = monthly interest rate = annual rate / 12 (as a decimal, e.g., 8% / 12 = 0.006667)
  • n = total number of payments = loan term in years × 12

This is the standard formula used by banks and loan calculators. It assumes a fixed interest rate for the entire term and that payments are made at the end of each month.

Worked Example

Let’s use the defaults from the calculator above: a $20,000 loan at 8% APR for 5 years.

  1. Convert APR to monthly rate: r = 8 / 100 / 12 = 0.0066667
  2. Total payments: n = 5 × 12 = 60
  3. Compute (1 + r)^n = (1.0066667)^60 ≈ 1.489846
  4. Payment = 20000 × [0.0066667 × 1.489846] / [1.489846 – 1] = 20000 × 0.0099336 / 0.489846 ≈ 405.53

So the monthly payment is $405.53 (rounded to cents). The total paid over 60 months is 60 × 405.53 = $24,331.80. The total interest is $24,331.80 – $20,000 = $4,331.80. These match the calculator outputs exactly.

Assumptions

  • Fixed annual interest rate (APR) for the entire loan term.
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at a rate of APR / 12.
  • No fees, taxes, insurance, or other charges are included.
  • The loan is fully amortized—meaning the final payment pays off the balance to zero.

How to Build It in Excel

You don’t need a pre-made template. Here’s a simple way to create your own amortization calculator in Excel:

  1. Set up input cells: In cells A1, A2, A3, enter labels for Loan Amount, Annual Interest Rate (%), and Loan Term (years). Put the values in B1, B2, B3.
  2. Calculate monthly payment: In cell B4, enter the formula: =B1*(B2/1200)/(1-(1+B2/1200)^(-B3*12)). Format as currency.
  3. Create the schedule header: In row 7, put column headers: Payment #, Payment, Interest, Principal, Balance.
  4. Fill the first row: In A8, put 1. In B8, put =$B$4. In C8, put =E7*(B$2/1200) (assuming E7 is the starting balance). In D8, put =B8-C8. In E8, put =E7-D8.
  5. Drag down: Copy row 8 down to row 67 (for 60 payments). Adjust the starting balance in E7 to equal the loan amount.

That’s it. The schedule will show how much of each payment goes to interest versus principal, and the balance will decrease to zero by the final payment.

How to Read an Amortization Schedule

An amortization schedule is a table that breaks down every payment into two parts: interest and principal. Early in the loan, a larger share of the payment goes to interest because the outstanding balance is high. As the balance drops, the interest portion shrinks and the principal portion grows. The total payment stays the same for a fixed-rate loan.

For example, in the $20,000 loan above, the first payment of $405.53 includes $133.33 of interest and $272.20 of principal. By the 60th payment, the interest is only about $2.68, and the principal is $402.85. This is why paying extra toward principal early can save significant interest over the life of the loan—though you should always check with your lender for prepayment rules.

Common Mistakes and Pitfalls

  • Using the annual rate in the monthly formula: Always divide the APR by 12 to get the monthly rate. Forgetting this will produce a wildly wrong payment.
  • Mixing up months and years: The term must be in months. If you enter 5 instead of 60, you’ll get a payment for a 5-month loan.
  • Rounding too early: Keep full precision in your Excel formulas. If you round the payment to cents before calculating the schedule, the final balance may not be exactly zero—that’s normal, but it can be confusing.
  • Ignoring extra payments: The basic formula assumes no extra payments. If you plan to pay extra, you’ll need to adjust the schedule manually or use a more advanced calculator.

When to Use Excel vs. an Online Calculator

Building your own Excel amortization calculator gives you full control. You can customize it to include extra payments, change interest rates mid-term, or add columns for escrow or PMI. It’s also useful for “what-if” scenarios—for example, seeing how a slightly higher down payment affects total interest.

However, online calculators like the one above are faster for a quick estimate. They require no setup and give you the payment and total interest instantly. For a one-off calculation, an online tool is fine. For ongoing planning or complex scenarios, Excel is more flexible.

One limitation of both methods is that they assume a fixed rate. Adjustable-rate mortgages or loans with changing payments require a different approach. Also, real loans often include fees, taxes, and insurance that are not part of the basic amortization formula. Those costs must be added separately.

Frequently Asked Questions

Can I get a free amortization calculator Excel template?

Yes, many websites offer free Excel templates for amortization schedules. However, you can also create one yourself in a few minutes using the steps above. A template might save time, but building your own ensures you understand exactly how it works and can customize it to your needs.

How do I add extra payments to my Excel amortization schedule?

To add extra payments, insert a column for “Extra Payment” and subtract it from the balance after the regular principal payment. For example, if your extra payment is in column F, the balance formula becomes =E8-D8-F8. You’ll need to adjust the final payment if the extra payment causes the loan to be paid off early.

Why does my amortization schedule not end at zero?

This usually happens because of rounding. The monthly payment is rounded to cents, but the actual payment needed to exactly amortize the loan has more decimals. A tiny balance (like $0.01 or $0.02) may remain. You can fix it by adjusting the final payment to clear the remaining balance, or by using more decimal places in your payment formula.

What is the difference between APR and interest rate?

APR (Annual Percentage Rate) includes the interest rate plus certain fees and costs, expressed as a yearly rate. The interest rate is just the cost of borrowing the principal. For amortization calculations, lenders typically use the interest rate to compute the payment, but APR can give a better picture of the total cost. Our calculator uses the APR as an input, but for a standard loan without fees, they are the same.

Can I use this calculator for a car loan or personal loan?

Yes, the formula works for any fixed-rate, fully amortizing loan—car loans, personal loans, student loans, and mortgages all use the same math. Just enter the loan amount, interest rate, and term. The only difference is the typical term length and whether there are any fees or balloon payments.

The result from this calculator is an estimate; your lender’s disclosure will provide the exact payment and total cost.

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

The whole Loan payments section →