Loan repayment calculator in Excel

What does this tool do?

Who is it for?

• Anyone considering a home loan, car loan or renovation loan who wants to know the monthly payment and total interest cost in advance.
• Households that want to try out different terms or amounts before approaching the bank.
• Self-employed people planning an investment loan who want to fit the repayments into their cashflow.
• Anyone with an existing loan who wants to know what an extra repayment or a refinancing saves in interest.

What is included?

• Input sheet with loan amount, term in months, nominal annual rate and start date, with a note next to every cell.
• Monthly payment, total amount repaid and total interest cost at a glance.
• Full monthly repayment schedule: interest, capital and outstanding balance after every payment.
• Extra or early repayments, one-off or monthly, with the interest saved and the shortened term (extended version).
• Comparison of three terms side by side: monthly payment, total interest and total repaid (extended version).
• Chart showing, year by year, how much of your payments goes to capital and how much to interest (extended version).

How do you use the file?

1. 1

   Open the file and go to the “Input” sheet. Only the yellow cells are meant to be filled in.

2. 2

   Enter the loan amount: the capital you actually draw down, so without your own contribution and without the costs you pay separately.

3. 3

   Enter the term in months (for example 240 for twenty years) and the nominal annual rate as it appears on your offer. Use the borrowing rate, not the APR.

4. 4

   Look at the “Overview” sheet: you immediately see the monthly payment, the total you repay and the total interest cost. Below it is the repayment schedule with interest, capital and balance per month.

5. 5

   In the extended version you also enter extra repayments on the “Input” sheet, one-off or monthly, and three terms you want to compare. The “Overview” sheet shows the interest saved and the shorter term.

6. 6

   Change the rate or the term to run scenarios and put the result next to your bank’s repayment schedule. Small differences are normal; large differences point to costs that are not in the nominal rate.

Worked example

A loan of €200,000 over twenty years at a fixed nominal rate of 3.5%, repaid in 240 equal monthly instalments. The figures are rounded and serve only as an illustration.

After ten years you have made 120 payments totalling €139,190, yet only €82,701 of the capital is repaid; the rest was interest. Borrowing for five years less costs €270 more per month but saves €21,023 in interest.

Sources & calculation method

This is how the calculation is built:

1. The monthly rate is the nominal annual rate divided by twelve. The monthly payment follows from the annuity formula: capital × monthly rate ÷ (1 − (1 + monthly rate) to the power of minus the number of months). In Excel that is the PMT function.
2. Each row of the repayment schedule calculates interest as outstanding balance × monthly rate. The capital share is the monthly payment minus that interest, and the new balance is the old balance minus the capital share. After the last month the balance is zero.
3. The total repaid is the monthly payment × the number of months. The total interest cost is that total minus the capital borrowed, and equals the sum of the interest column.
4. An extra repayment (extended version) is deducted from the balance in the month it is paid. The monthly payment stays the same, so the loan ends earlier; the file counts the interest and the months saved. Any early repayment indemnity is entered separately.
5. The term comparison applies the same formula to three terms and puts monthly payment, total interest and total repaid side by side. The chart shows, year by year, the share of capital and interest in your payments.

Assumptions

• The file works with the nominal annual rate divided by twelve. Belgian lenders often use an actuarial annual rate, where the monthly rate is slightly lower; in the example that gives €1,154.28 instead of €1,159.92 per month. The difference is small, but it explains why your schedule may differ by a few euros from the bank’s.
• The rate stays fixed for the whole term. You simulate a variable rate by changing the rate and comparing the results; the file does not calculate revisions automatically.
• The first payment falls one month after the capital is drawn down and every payment is made at the end of the month.
• File fees, outstanding balance insurance and notary costs are not in the monthly payment. To compare offers with all their costs, use the loan comparison Excel.

Limitations

• The file is neither a credit offer nor financial advice; only the lender sets the final conditions.
• It only handles loans with fixed monthly capital repayment, not credit lines, bullet loans or loans with capital reconstitution.
• Tax benefits, such as a regional tax reduction for a home loan, are not calculated.

Sources

This page and the file were last updated on September 24, 2026. Legislation and rates may have changed since.

Frequently asked questions