How to Build a Loan Calculator in Excel (with Amortization Schedule)

Before you sign a loan agreement, you should know three numbers: the monthly payment, the total interest and the date you will finish. The lender's brochure often shows only the first. Excel gives you all three in minutes, and lets you compare offers side by side.

Step 1: Gather the inputs

Put four cells at the top of a sheet: loan amount, annual interest rate, term in months and payment type (annuity or declining balance). Everything else should refer to those cells.

Step 2: Calculate the monthly payment (annuity)

With an annuity, every payment is the same. Use PMT:

=-PMT(rate/12, months, amount)

For a $20,000 loan at 7.2 percent over 60 months: =-PMT(0.072/12,60,20000) gives $397.91 per month. Total paid is 397.91 × 60 = $23,874.83, so total interest is $3,874.83.

Step 3: Build the amortization schedule

One row per month. Excel has functions for each part of a payment:

• Interest: =-IPMT(rate/12, n, months, amount)
• Principal: =-PPMT(rate/12, n, months, amount)
• Balance: previous balance minus principal

Here is how the example begins:

In month 1, interest is 20,000 × 0.6 percent = $120.00. As the balance falls, interest falls and more of each payment goes to principal. At month 60 the balance should be zero, which is a good check on your formulas.

Step 4: Annuity or declining balance?

With annuity payments, the instalment is equal throughout. With declining balance (also called equal principal), the principal is divided equally: 20,000 ÷ 60 = $333.33 each month, plus interest on the remaining balance. The first payment is higher ($453.33), the last is lower ($335.33), and total interest is lower: $3,660 instead of $3,874.83. The saving is modest, but the early payments are heavier, so check that your budget can take them.

Step 5: Model early repayments

Add an extra-payment column to the schedule. When an extra amount is paid in a month, subtract it from the balance as well. You then have two choices:

• Reduce the term. Keep the same payment; the loan ends earlier. This usually saves the most interest.
• Reduce the payment. Keep the term; recalculate the payment on the new balance with =-PMT(rate/12, remaining_months, balance).

Check your agreement first for fees or restrictions on early repayment.

Step 6: Compare offers properly

Do not compare rates alone. Compare total repayment, which includes fees and insurance, between offers. Enter each offer in its own column. Two loans with the same nominal rate can cost quite different amounts once fees are added.

Common mistakes

• Dividing the annual rate by 12 without matching the payment frequency. For weekly or biweekly payments the periods change.
• Forgetting fees and insurance. They are real costs that the standard formulas do not include.
• Comparing loans of different terms by payment alone. A lower payment over a longer term usually costs more in total.
• Ignoring rounding. Lenders round each payment, so the last instalment differs slightly.
• Trusting any spreadsheet over the contract. The lender's schedule is the final word.

Free ready-made template

The “Loan Calculator with Payment Schedule” template is free and handles all of the above.

• “Calculation” takes the amount, rate, term and payment type, and shows the results, a table and a chart by year.
• “Extra payments” holds up to 60 early repayments.
• “Schedule” is the full schedule up to 360 months with interest, principal and balance.

After an early repayment you can choose what to reduce, the term or the payment. The download includes an empty template and a filled-in sample with fictitious data. It works in Excel 2007 and later and in LibreOffice, with ordinary formulas and no macros. The calculation is indicative: a lender may calculate interest by day and add fees, so rely on the lender's schedule.

FAQ

What is the difference between interest rate and APR?

The interest rate is the cost of borrowing the principal. APR also includes certain fees, so it better reflects the true cost. Use APR to compare offers where it is available.

Can I use the calculator for a car loan or a student loan?

Yes, for any loan with regular payments and a fixed rate.

How do I handle a variable rate?

Calculate to the date of the change, then restart the calculation with the remaining balance, new rate and remaining term.

What is the maximum term?

The template supports up to 360 months, which covers most mortgages.