Ladrillómetro
ES
✎

Mortgage amortisation table in Spain (French system)

How each payment splits into interest and principal, a €200,000 example over 30 years at 3%, how to build it in Excel and how to download it as CSV from the calculator.

FINANCING · 10 MIN READ

A mortgage amortisation table in Spain (tabla de amortización hipoteca) splits each payment into interest and principal, and shows how much you still owe. Under the French system, the usual one for Spanish mortgages, the payment is fixed: early on it goes more to interest, and later more to principal. On a €200,000 loan over 30 years at 3%, the payment is €843.21 a month and total interest about €103,555; you can view the full schedule, by year or by month, and download it as CSV in the mortgage calculator.

Below is the worked example, how to read it, how to build it in Excel, and what changes if you repay early or if the rate is variable.

What is a mortgage amortisation table?

It is the loan calendar: for each payment it shows how much you pay, how much of that is interest, how much reduces the principal and how much is still outstanding. It does not change the contract; it only turns it into numbers. If you borrow €200,000, you do not repay €200,000: you repay the principal plus the interest charged each month on the balance still outstanding.

Most home mortgages in Spain use the French amortisation system: a constant regular payment (if the rate does not change) and a split that gradually tilts towards principal. It is not the only system, but it is the one the mortgage calculator uses and the one you will see in almost any fixed-rate offer.

How is the payment calculated under the French system?

The monthly payment formula is payment = L × r ÷ [1 − (1 + r)−n], where L is the loan, r the annual rate divided by 12 and n the number of payments. In the example, L = 200,000, r = 0.03 ÷ 12 = 0.0025 and n = 360, so the payment is €843.21. It is the same figure already used in the guide to mortgage payment by loan amount and term and in the calculator.

Each month, interest is charged on the outstanding balance: balance × 0.03 ÷ 12. The rest of the payment goes to principal. In the first month, 200,000 × 0.0025 = €500 of interest and €343.21 of principal; you then owe €199,656.79. In the second month interest falls to €499.14, because it is calculated on that lower balance, and principal rises to €344.07. That continues until the last payment, which is adjusted to the nearest cent so the balance ends at zero.

Interest over the 360 payments adds up to €103,554.53. In the calculator summary, which multiplies the unrounded payment by 360, the figure is about €103,555: the gap is a few cents from rounding each instalment. This guide uses “about €103,555” for the total and the exact cents when we talk about a specific row.

How do you read the table month by month and year by year?

The monthly view is for a single payment (for example, one on a bank statement). The yearly view adds up the 12 payments in each year: the amount paid, interest, principal repaid and the balance at the end of that year. In the example, in year 1 you pay €10,118.52, of which €5,942.90 is interest and €4,175.62 principal; you still owe €195,824.38. Principal does not overtake interest until year 8.

Principal and interest by year in a Spanish mortgage amortisation table under the French system: a €200,000 loan over 30 years at 3%, €843.21 a month and €103,555 in total interest; principal overtakes interest from year 8
Principal and interest in each year of the €200,000 loan over 30 years at 3%. The height of each bar is the amount paid that year. Original Ladrillómetro chart.

Selected rows of the yearly summary (the cents are the sum of that year’s 12 payments):

YearPaidInterestPrincipalBalance
1€10,118.52€5,942.90€4,175.62€195,824.38
5€10,118.52€5,411.23€4,707.29€177,812.59
8€10,118.52€4,968.49€5,150.03€162,814.07
10€10,118.52€4,650.43€5,468.09€152,039.31
15€10,118.52€3,766.72€6,351.80€122,100.68
20€10,118.52€2,740.15€7,378.37€87,323.45
25€10,118.52€1,547.67€8,570.85€46,925.66
30€10,117.45€162.50€9,954.95€0

After 10 years you have paid about €101,185 and still owe €152,039: more than three-quarters of the loan. That is not a mistake; it is the result of the early years going mostly to interest. In the last year the annual total drops by about a euro (to €10,117.45) because the final monthly payment is adjusted to €842.14 to clear the balance.

Open this example in the calculator

To reproduce it: price €200,000, down payment 0, rate 3% and term 30 years. If your mortgage is different, change those fields and the table recalculates.

How do you make a mortgage amortisation table in Excel?

If you want to build it yourself, the payment in Excel (or LibreOffice Calc) is =PMT(rate/12; n; −loan) (in Spanish Excel, PAGO). With 3%, 360 months and €200,000: =PMT(3%/12, 360, −200000), which gives €843.21. In each row, that month’s interest is the previous balance times rate/12, principal is the payment minus that interest, and the new balance is the previous one minus the principal. IPMT and PPMT (PAGOINT and PAGOPRIN) can split a single period, but a month-by-month table is clearer with those three columns.

Two details so that it lines up with the bank and with the calculator: round each payment and interest amount to the nearest cent, and adjust the last row to the remaining balance. If you open the file in Spanish Excel, use a semicolon as the separator (that is the CSV the calculator downloads). A gap of a few cents versus the schedule in the deed usually comes from rounding or from a nominal rate versus the APR; the table does not replace the deed.

What happens if you repay early?

The outstanding-balance column is, precisely, what you would have to repay if you cancelled on that date (before any fees). If you make a partial early repayment, the bank recalculates the schedule: either the payment falls and the term stays, or the term shortens and the payment stays. The interest you “save” is the interest on the payments that will no longer exist, not a fixed figure you get by subtracting from €103,555. We run the numbers, including the legal fee and the alternative of investing that money, in the guide Pay off the mortgage or invest?.

On a variable-rate mortgage the table you see today is a snapshot at the current rate: when Euribor is reviewed, both the payment and the split of the remaining balance change. The pattern (more interest first, more principal later) stays, but the figures for later years are not fixed. You can compare options in fixed, variable or mixed mortgages.

Where can you download the amortisation table as CSV?

In the mortgage calculator, below the payment estimate, you will find the table: you can view it by year or by month (or by each payment, if you choose another payment frequency) and click “Download CSV”. The file includes every payment, with year, amount paid, interest, principal and balance, and opens in Excel, Google Sheets or LibreOffice. It is free, with no sign-up, and it is calculated in your browser: the loan figures are not sent to a server.

Results are indicative and are not a lending offer or the binding schedule in your deed.

Run your own numbers

Enter your price, down payment, rate and term. The table updates at once and you can take the CSV with you. Free, no sign-up.

Open the mortgage calculator

For information only; this is not financial or tax advice.

FREQUENT QUESTIONS

Frequently asked questions

Does the amortisation table change if the rate is variable?

Yes. The table is calculated with today’s rate on the outstanding balance. When the mortgage reviews Euribor, the bank recalculates the payment and the split of what is left; later rows of a table made today no longer apply.

Why is almost everything interest at the beginning?

Because interest is charged each month on what you still owe, and at the start that balance is the whole loan. In the example, the first €843.21 payment includes €500 of interest. As you pay down principal, that share shrinks and the principal share grows.

Do the CSV and Excel match the bank’s figures?

They should come very close if you use the same loan, rate, term and rounding to the nearest cent. A gap of a few cents is common. The binding schedule is the one in the deed, not the calculator or a spreadsheet.

Can I shorten the term or cut the payment with a partial early repayment?

Yes, you usually choose one of the two: the same payment and fewer years, or the same term and a lower payment. The table tells you the balance on which that is recalculated. We compare it with investing that money in the pay-off-or-invest guide.

Is the last payment the same as the others?

Almost, but not always to the cent. To bring the balance to zero, the last payment is adjusted: in the example it is €842.14 instead of €843.21. That is why the last year adds up to €10,117.45 rather than €10,118.52.

More guides