Amortisation schedule in Excel: what does the free file calculate with your figures?
An amortisation schedule in Excel is not a printout but a tool: change one number and the whole schedule recalculates. The file is therefore a complete repayment calculator in Excel — five input fields, 480 monthly rows, special repayments included. Free, no macros, all formulas intact, labels in German and English. If you would rather calculate in the browser straight away, 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.
The file
The special repayment is applied at the end of every twelfth month, and only as far as an outstanding balance remains.
The calculation uses the borrowing rate, not the effective annual rate; purchase costs and commitment interest are not included. Model calculation without guarantee, no financing commitment and no tax advice.
Two sheets: “Tilgungsplan” with the inputs, the results and the full schedule, and “Hinweise” with instructions and the limits of the calculation. Every label appears in German and English. No macros, no external links, no sheet protection — you can inspect every formula and reuse every row.
Open the Excel file directly — no form: Amortisation schedule in Excel (2 sheets, German/English) · 55 kB
What do you enter?
Only five cells are inputs; they have a yellow background and sit one below the other at the top of the sheet. Everything else is calculated from them.
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. Enter the rate from your own offer — then the file calculates your case.
What does the file calculate from it?
The file calculates, among other things, the monthly instalment, the outstanding balance at the end of the fixed-rate period and the interest during the fixed-rate period.
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.
Three ways to get more out of the file
You get more out of the file by comparing variants side by side, playing through the special repayment and taking your own calculation to the bank meeting.
Compare variants side by side. Save the file twice — once with two, once with three percent initial repayment — and compare not the instalments but the outstanding balance at the end of the fixed period. That is the difference that counts later. Play through the special repayment. Enter the amount you can realistically put aside each year and see how many months drop off at the end.
Many contracts allow special repayments free of charge — they just have to be agreed and then used. Take it to the bank meeting. If you arrive with your own calculation, you discuss the right figures. If the bank's schedule differs, it is almost always because of one of the five inputs — and it is quickly clear which one.
What is the file not suitable for?
Among other things, the file does not calculate the effective annual rate, purchase costs or commitment interest.
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 online repayment calculator handles a KfW component. No commitment. Model calculation without guarantee. Only the bank's final offer states binding terms; they depend on credit standing, loan-to-value and the lender.
Keep this result — and the right documents: A calculator gives you a number. A financing decision needs the right paperwork ready before you talk to a bank. We'll send you the checklist that matches your situation — free, no sales call attached.
New: The amortisation schedule also comes as an Excel file to keep — with your figures and all formulas intact. Change the rate, the repayment or the overpayment and the whole plan recalculates. Not a frozen printout.
Need the blank forms right now? Get them via a quick form: self-disclosure form (DE/EN) · net-worth statement (DE/EN).
Open the file directly — no form: Germany property financing checklist (PDF, 9 pages) · Non-resident mortgage checklist (PDF, 9 pages) · KfW funding overview 2026 (PDF, 4 pages) · Amortisation calculator (Excel)
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.
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. The effective annual rate under the German PAngV additionally covers costs and payment dates and is higher. Purchase costs and commitment interest are not included in the file either.
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.
Related pages
Repayment calculator
The same calculation in the browser, with a KfW component and the result by email.
Editorially responsible: Olga Nikushkina — owner, §34i GewO (German mortgage brokerage), adviser register D-W-132-ZUCB-95. Specialised in German property financing with an international dimension: non-residents, foreign-currency income, cross-border workers, expats. Siegfried Perini is entered in the German intermediary register under this licence as a person directly involved in brokerage and advice (§ 11a GewO), active in real estate since 1992. Not legal or tax advice.