Skip to main content
☰
Calculator · Amortisation schedule in Excel

Free amortisation schedule in Excel: calculate your loan with extra repayments

Use the Excel loan amortisation calculator to build a repayment schedule and save different scenarios. Change an input and the full schedule recalculates. The free file contains five input fields, 480 monthly rows and annual extra repayments. It has no macros, includes all formulas and uses German and English labels. If you prefer to calculate in your browser, use the online repayment calculator.

What does the amortisation schedule in Excel calculate?

The amortisation schedule as an Excel file calculates your loan in full. You enter five values: loan amount, borrowing rate, initial repayment rate, fixed-rate period and an annual special repayment. The sheet then calculates the monthly annuity, the outstanding balance at the end of the fixed-rate period, the interest and principal within it, and the months until full repayment.

Which documents would help?

Free · 30 seconds · no sales pressure

We use your email address to send the files you request. If needed, we may contact you personally about your request.

What is in the file?

The file has two worksheets. ‘Tilgungsplan’ contains the inputs, results and full repayment schedule. ‘Hinweise’ explains how to use it and the limits of the calculation. There are no macros, external links or protected sheets. You can inspect every formula and reuse every row.

Open the Excel file directly — by email: Repayment schedule in Excel — repayment calculator to keep (Excel, 2 worksheets) (Dutch (repayment)) · 55 kB

Loan amount. The net loan (Nettodarlehen) without acquisition costs — that is, the amount the bank actually pays out.

Borrowing rate per year. The fixed borrowing rate, not the effective interest rate. Enter as a percentage.

Initial repayment per year. Two to three per cent is usual; together with the borrowing rate it gives the instalment.

Fixed-rate period in years. It is followed by the follow-up financing. The fixed-rate period limits the result rows, not the table.

Extra repayment per year. Applied at the end of every twelfth month; 0 means no extra repayment.

The file comes with a preset example: a €350,000 loan, 3.20 % borrowing rate, 2.00 % initial repayment, ten-year fixed-rate period, no extra repayment. All five values are meant to be overwritten — they are assumptions for illustration, not terms. The preset borrowing rate is deliberately a round calculation figure and not a market rate: it is below our current interest rate band and says nothing about the rate at which you can finance. Enter the rate from your own offer — then the file calculates your case.

Which cells do you fill in?

Enter your figures in the five yellow cells at the top of the sheet. They are arranged one below the other. All other values are calculated from these inputs.

Loan amount. The net loan without purchase costs — the amount the bank actually pays out.

Borrowing rate per year. The fixed borrowing rate, not the effective annual rate. Enter it as a percentage.

Initial repayment rate per year. Two to three percent is usual; together with the borrowing rate it sets the instalment.

Fixed-rate period in years. The follow-on financing comes after it. The fixed-rate period limits the result rows, not the table.

Special repayment per year. Applied at the end of every twelfth month; 0 means no special repayment.

The file comes with a preset example: EUR 350,000 loan, 3.20 % borrowing rate, 2.00 % initial repayment, ten-year fixed-rate period, no special repayment. All five values are meant to be overwritten — they are illustrative assumptions, not terms.

The preset borrowing rate is deliberately a round calculation value and not a market rate: it is below the current market range and says nothing about the rate at which you can finance.

Which results does the file give you from your five entries?

The file calculates the monthly instalment, the remaining balance at the end of the fixed-rate period and the interest paid during that period, among other results.

The monthly instalment. The annuity from borrowing rate plus initial repayment, divided by twelve, times the loan amount — constant over the fixed-rate period.

The outstanding balance at the end of the fixed-rate period. The amount the follow-on financing has to take over. The file picks exactly the schedule row for the last month of the fixed period.

Interest during the fixed-rate period.

The sum of all interest portions up to the end of the period — the figure that is missing when offers are compared by instalment only.

Principal repaid during the fixed-rate period. The sum of the principal portions, special repayments included.

Months until full repayment. Assuming an unchanged rate over the whole term — a model assumption. If the 480 rows are not enough, the file says “over 480” instead of an invented number.

Below that the actual schedule begins: one row per month, 480 rows deep, with month, year, opening balance, interest portion, principal portion, special repayment and closing balance. The special repayment only appears in every twelfth month and only as far as a balance remains; once the loan is repaid, the following rows stay at zero instead of turning negative.

Does the Excel calculator include annual extra repayments?

Yes. Annual extra repayment is one of the five inputs. Change the amount and the full schedule recalculates, free of charge and without macros.

How many years does the Excel annuity loan calculator cover? 480 monthly rows, i.e. up to 40 years — month by month, showing interest, capital repayment, extra repayments and the remaining balance.

Where can you see when the loan will be paid off? In the repayment schedule as an Excel file: It calculates your loan in full using your own figures, row by row, right up to the final instalment.

Keep the result and prepare your documents: For a financing decision, you need the right paperwork as well as the calculation. We will send you the checklist for your situation free of charge, with no follow-up sales call.

Keep the Excel file: Your repayment schedule includes your figures and all formulas. When you change the interest rate, repayment rate or extra repayment, the full schedule recalculates. You can then compare and save different scenarios.

Do you need the blank forms straight away? Simply request them using the form: Self-disclosure (DE/EN) · Asset and liabilities statement (DE/EN).

Open file directly — by email: Mortgage Documentation — Checklist (PDF, 9 pages) (Dutch (Germany) · French (Germany)) · Mortgage for non-residents for tax purposes — Checklist (PDF, 9 pages) (Dutch (non-residents) · French (non-residents)) · KfW Funding Overview 2026 (PDF, 4 pages) (Dutch (KfW)) · Amortisation Schedule in Excel — Amortisation Calculator (Excel) (Dutch (amortisation))

How can you use the file to compare financing options?

Compare scenarios using the remaining balance, calculate the effect of extra repayments and take your own figures to the bank meeting. The file does not include the effective interest rate, purchase costs, commitment interest (Bereitstellungszinsen) or funding components.

Compare scenarios: Save two copies, one with two and one with three per cent initial repayment. Compare the remaining balance at the end of the fixed-rate period as well as the instalments.

Calculate extra repayments: Enter the amount you can realistically pay each year and check how many months it removes from the repayment term.

Many contracts allow extra repayments free of charge, provided you agree this right and use it.

Take your calculation to the bank: It helps you discuss the figures that matter. If the bank's schedule differs, the reason is almost always one of the five inputs. Comparing them makes the difference easier to identify.

What the file is not suitable for: No effective interest rate. The calculation uses the borrowing rate. The effective annual interest rate under the PAngV additionally includes costs and payment dates and is higher.

No acquisition costs. Land transfer tax, notary, land register and estate agent are not included — the bank usually does not finance them anyway. For that there is the acquisition costs calculator. No commitment interest.

For new builds it is charged after the commitment-free period; the file does not show it.

No subsidy components. A KfW portion with its own term and its own repayment is not shown separately. The mortgage calculator can calculate a separate KfW component. Binding terms are given only in the bank’s final offer; they depend on creditworthiness, lending value and the bank.

How does the extra repayment calculator apply an annual payment?

The additional repayment is applied at the end of each twelfth month, and only to the extent that there is still an outstanding balance. Enter the amount per year; 0 means no additional repayment. The additional repayments are factored into the repayment schedule within the fixed-interest period. As all the formulas remain in the file, any changes are reflected immediately.

Repayment calculator with unscheduled repayments: When are unscheduled repayments applied? Unscheduled repayments are applied at the end of every twelfth month, and only to the extent that there is still any outstanding debt. It therefore only appears in the repayment schedule every twelfth month. Once the loan has been repaid, the subsequent rows remain at zero.

Annuity loan calculator with unscheduled repayments: How does the outstanding balance change at the end of the fixed-rate period? The unscheduled repayments are included in the capital repayments, so the outstanding balance at the end of the fixed-rate period takes them into account. To do this, the file takes the schedule row for the final month of the fixed-rate period; this is the amount that the follow-up mortgage must cover. To compare, save the file with and without the extra repayment and compare the remaining balance.

What is the file not suitable for?

The file does not calculate the effective annual rate, purchase costs or commitment interest, among other limitations.

No effective annual rate. The calculation uses the borrowing rate. The effective annual rate under the German PAngV additionally covers costs and payment dates and is higher.

No purchase costs. Real-estate transfer tax, notary, land register and estate agent are not included — banks usually do not finance them anyway. For those, use the purchase cost calculator. No commitment interest.

On a new build it accrues once the commitment-free period ends; the file does not show it.

No funding components. A KfW tranche with its own term and repayment is not tracked separately. The mortgage calculator can calculate a separate KfW component. Only the bank's final offer states binding terms; they depend on credit standing, loan-to-value and the lender.

Keep the Excel repayment schedule: The file includes your figures and all formulas. Change the interest rate, repayment rate or extra repayment to recalculate the full schedule and compare different assumptions.

Need the blank forms right now? Get them via a quick form: self-disclosure form (DE/EN) · net-worth statement (DE/EN).

Request by email: Germany property financing checklist (PDF, 9 pages) (Dutch (Germany) · French (Germany)) · Non-resident mortgage checklist (PDF, 9 pages) (Dutch (non-residents) · French (non-residents)) · KfW funding overview 2026 (PDF, 4 pages) (Dutch (KfW)) · Amortisation calculator (Excel) (Dutch (repayment))

Frequently asked questions

Do I need Microsoft Excel?

No. The file is an ordinary .xlsx workbook without macros; LibreOffice Calc, Google Sheets, Apple Numbers and the free Excel web versions open and calculate it as well. The two sheets are called “Tilgungsplan” and “Hinweise”; all labels are in German and English.

Annuity loan calculator in Excel: Which five figures do you enter?

Enter the loan amount (net, excluding purchase costs), the annual borrowing rate, the initial annual repayment, the fixed-rate period in years and the annual unscheduled repayment. These are the only five input cells; they are highlighted in yellow, and everything else is calculated automatically. The calculation is based on the nominal interest rate, not the effective interest rate.

How do I enter the rate — 3.2 or 0.032?

As a percentage. The cells are formatted as percentages; internally the sheet works with the decimal value. If you enter the decimal by mistake, you will see it straight away from an implausibly low instalment.

When is the special repayment applied?

At the end of every twelfth month — once per loan year — and only as far as an outstanding balance remains. How much your contract allows is stated in the contract; 5 % of the loan amount per year is common. With no entry (0) the file calculates without special repayments.

Why does the schedule continue beyond the fixed-rate period?

Because the fixed-rate period only limits the result rows at the top, not the table. Beyond it, the schedule shows how things would continue if the rate stayed the same — a model assumption, not a forecast. What actually follows is the follow-on financing at the terms then applicable.

Is the result the effective annual rate?

No. The calculation uses the borrowing rate. Under the German PAngV, the effective annual rate also factors in costs and payment timing, which pushes it higher. Purchase costs and commitment interest are not included in the file either.

Repayment table by term: How long does the loan run until it is fully repaid?

The file calculates the months until full repayment, assuming an unchanged interest rate over the whole term; this is a model assumption. If the 480 rows are not enough, it shows “over 480” instead of an invented figure. The schedule follows below, month by month.

Any questions on this topic?

A first consultation is free and without obligation — the commission is, as a rule, paid by the bank. We tell you what this means for your own financing.
No loan approval. Model calculation without guarantee. No commitment.

For a property loan, most banks only arrive at a workable calculation from a household net income of around €3,500 a month — for couples, the joint income counts.

Bring the loan amount, rate, term and additional repayments you entered in the spreadsheet. We can compare the remaining balance at the end of the fixed period with and without the extra payments.

Related pages

Online

Repayment calculator

Calculate the instalment, remaining balance and interest costs in your browser — without a separate KfW component, with the result by email.

Documents

Mortgage documents checklist

Which documents the bank wants to see — complete on the page.

Overview

All calculators

Budget, purchase costs, fixed-rate period, rental yield and more.