Spanish Mortgage Payment Calculation

Hello! Welcome to the second module of our course.

In the last lesson, we successfully built a Python model to calculate Net Operating Income (NOI). This gave us a clear picture of a property's profitability based purely on its operations, before considering any financing. However, since most real estate investments are made with borrowed money, our next step is to incorporate the cost of that debt.

This lesson directly addresses that by focusing on how to calculate monthly mortgage payments (debt service) using loan amount, interest rate, and term specific to Spanish market conditions. This payment, often called debt service, is typically the largest single cash outflow for a leveraged property investor. Understanding how it's calculated is fundamental to analyzing any real estate deal.

1. The French Amortization System: The Standard in Spain

When you get a mortgage in Spain, it's almost certain to use the "sistema de amortización francés" (French amortization system). Its defining characteristic is a constant monthly payment throughout the life of the loan (assuming a fixed interest rate).

While the total payment stays the same, the composition of that payment changes each month:

• In the early years, a larger portion of your payment goes towards paying interest.
• In the later years, a larger portion goes towards paying down the principal loan amount.

This front-loading of interest is why you'll often hear that "at first, you’re mostly paying the bank."

The constant monthly payment (PMT or cuota mensual) is calculated using a standard formula.

As you saw in the reading, the formula is:

$$\text{PMT}=P\times \frac{r(1+r{)}^n}{(1+r{)}^n-1}$$

Where:

• $P$ = The total loan amount (the principal).
• $r$ = The periodic interest rate (the annual rate divided by the number of payments per year). For a monthly mortgage, this is the annual interest rate divided by 12.
• $n$ = The total number of payments (loan term in years multiplied by 12).

Given your background, you might appreciate that this formula isn't arbitrary. It's the result of solving a recursive equation that describes the loan balance month after month.

2. The Interest Rate in Spain: TIN vs. TAE

Before we can use the formula, we must clarify a crucial point for the Spanish market: which interest rate to use for $r$. When you look at mortgage offers in Spain, you will always see two rates: TIN and TAE.

• TIN (Tipo de Interés Nominal): The nominal interest rate. This is the "price" of the money you're borrowing. It's the rate used in the mortgage payment formula.
• TAE (Tasa Anual Equivalente): The annual equivalent rate. This rate includes the TIN plus most of the other costs associated with the loan, such as opening fees (comisión de apertura), mandatory insurance products, etc.

The TAE is a comparison tool required by Spanish law to help you understand the true total cost of different loan offers. However, for calculating the monthly payment, we always use the TIN.

3. Calculating the Monthly Payment: A Worked Example

Let's apply this knowledge. We'll use the example from the estebansastre.com article you read earlier.

• Loan Principal ($P$): €100,000
• Annual Fixed TIN: 3.5%
• Term: 25 years

Step 1: Define the variables

• $P=100,000$
• Annual TIN = 0.035
• Monthly interest rate ($r$): $0.035/12\approx 0.00291667$
• Total number of payments ($n$): $25\text{ years}\times 12\text{ months/year}=300$

Step 2: Apply the formula

$$\text{PMT}=100,000\times \frac{0.00291667(1+0.00291667{)}^{300}}{(1+0.00291667{)}^{300}-1}$$

Plugging this into a calculator gives:

$$\text{PMT}\approx €500.84$$

This is the fixed monthly payment for the next 25 years.

Step 3: Analyze the first payment

• Interest Portion: $\text{Remaining Balance}\times r=100,000\times 0.00291667\approx €291.67$
• Principal Portion: $\text{PMT}-\text{Interest Portion}=500.84-291.67\approx €209.17$
• New Balance: $100,000-209.17=€99,790.83$

For the second month, the interest would be calculated on the new, slightly lower balance, meaning a tiny bit less interest is paid and a tiny bit more principal is paid down. This process repeats every month, as shown in an amortization schedule.

4. Fixed vs. Variable Rates in the Spanish Market

The example above uses a fixed TIN (tipo fijo). However, many mortgages in Spain have a variable TIN (tipo variable). These are typically structured as:

Variable TIN = Euribor + Differential (diferencial)

• Euribor: The Euro Interbank Offered Rate. It's a benchmark rate that fluctuates with the market. The 12-month Euribor is most common for Spanish mortgages.
• Differential: A fixed percentage added by the bank. This is the bank's margin.

With a variable-rate mortgage, the TIN (and therefore your monthly payment) is recalculated at set intervals, usually every 6 or 12 months, based on the latest Euribor value.

Test your understanding!

A Spanish bank offers you a mortgage for a property investment.

• Loan Amount (P): €220,000
• Term: 30 years
• Interest Rate: A fixed annual TIN of 2.8%

What will your fixed monthly mortgage payment be?

Show answer

1. Identify variables:

   • $P=220,000$
   • Annual TIN = 0.028
   • Monthly rate $r=0.028/12\approx 0.00233333$
   • Number of payments $n=30\times 12=360$

2. Apply the formula:

   $$\text{PMT}=220,000\times \frac{0.00233333(1.00233333{)}^{360}}{(1.00233333{)}^{360}-1}$$ $$\text{PMT}\approx 220,000\times \frac{0.00233333\times 2.313498}{2.313498-1}$$ $$\text{PMT}\approx 220,000\times \frac{0.00539816}{1.313498}\approx 220,000\times 0.0041101$$ $$\text{PMT}\approx €904.22$$

   Your monthly mortgage payment will be approximately €904.22.

5. From Formula to Code

As a software engineer, you'll want to automate this. We can translate the formula and the logic for generating an amortization schedule directly into Python.

A professional approach would be to use a dedicated financial library. numpy-financial is a standard choice that has a pre-built pmt function.

Here is a clean implementation using numpy-financial. You'll need to install it first: pip install numpy-financial.

import numpy_financial as npf

# --- Inputs ---
principal = 220000  # Loan amount in Euros
annual_tin = 0.028  # 2.8% annual TIN
years = 30          # 30-year term

# --- Calculations ---
# Convert to monthly rate and number of periods
monthly_rate = annual_tin / 12
n_periods = years * 12

# Calculate the monthly payment
# npf.pmt returns a negative value as it's a cash outflow. We use abs() for a positive number.
monthly_payment = abs(npf.pmt(rate=monthly_rate, nper=n_periods, pv=principal))

# --- Output ---
print(f"--- Mortgage Payment Calculation ---")
print(f"Loan Amount:         €{principal:,.2f}")
print(f"Annual TIN:          {annual_tin:.2%}")
print(f"Term:                {years} years")
print("---------------------------------------")
print(f"Monthly Payment:     €{monthly_payment:,.2f}")

# Example: Calculate interest for the first month
first_month_interest = principal * monthly_rate
print(f"Interest in Month 1:   €{first_month_interest:,.2f}")

# Example: Calculate principal paid in the first month
first_month_principal = monthly_payment - first_month_interest
print(f"Principal in Month 1:  €{first_month_principal:,.2f}")

This code is clean, reliable, and uses industry-standard tools for financial calculations—a perfect addition to the financial models we'll be building.

Conclusion

In this lesson, you have learned the mechanics behind mortgage payments, a critical component of real estate investment analysis.

Key Takeaways:

• Mortgages in Spain typically follow the French amortization system, characterized by a constant monthly payment.
• The monthly payment formula requires the principal, the number of periods, and the periodic interest rate, which is derived from the TIN (Tipo de Interés Nominal).
• The TAE (Tasa Anual Equivalente) is a tool for comparing the total cost of different loans but is not used to calculate the monthly payment itself.
• We can easily model this calculation in Python, with the numpy-financial library offering a robust and straightforward solution.

Preview of the Next Lesson:
Now that we can calculate our debt service, the next logical question is: how much debt should we take on? In the next lesson, we will explore the Loan-to-Value (LTV) ratio, a key metric used by lenders to determine the maximum loan amount they are willing to offer and by investors to measure their leverage.