How to calculate your mortgage payment is the first question you should answer before even setting foot in a bank, because it completely changes the conversation: instead of asking how much they are willing to lend you, you start deciding how much you want to pay each month. Your mortgage payment depends on three variables (the amount you borrow, the repayment term and the interest rate you are charged) and can be calculated using a formula that takes just a minute to solve. Here we explain it, with real examples and the details that explain why the bank's figure does not always match yours.
Factors that determine how to calculate a mortgage
Before applying any formula, it is important to understand what drives the result. These five factors explain why two people borrowing the same amount can end up paying very different monthly payments.
Loan amount and savings contributed
The loan amount is the money you actually borrow, not the price of the property. Most lenders finance up to 80% of the appraised value or purchase price, so you need to contribute the remaining 20% plus approximately another 10% for taxes and expenses. The more savings you contribute, the lower the loan amount and the lower your monthly payment will be.
Repayment term
Extending the term reduces the monthly payment, but makes the mortgage more expensive overall because you pay interest for more years. Increasing the term from 25 to 30 years can reduce your payment by around 80 euros per month while adding more than 20,000 euros in total interest.
Interest rate: nominal rate and APR
The nominal interest rate is the pure interest rate used to calculate the monthly payment. The APR includes fees and other costs, so it is useful for comparing offers, not for calculating the payment itself. If you mix the two, your result will not match.
Repayment system
In Spain, the French amortization system is used in most cases: the payment remains constant, with a larger proportion going towards interest and a smaller proportion towards principal at the beginning, and the opposite towards the end. This is the system assumed by virtually all mortgage calculators.
Linked products and discounts
Having your salary paid into the bank, taking out insurance or contributing to a pension plan can reduce the interest-rate spread by several tenths of a percentage point. Be careful: the savings in interest should exceed the cost of those products.
The French amortization formula
This is the formula used to determine how to calculate your mortgage payment with a constant monthly payment:
Payment = C × i / (1 − (1 + i)⁻ⁿ)
Where C is the amount borrowed, i is the monthly interest rate (the annual nominal interest rate divided by 12) and n is the total number of payments (the number of years multiplied by 12).
Let's look at a specific example: 200,000 euros over 25 years with a nominal interest rate of 3%.
- i = 0.03 / 12 = 0.0025
- n = 25 × 12 = 300
- Payment = 200,000 × 0.0025 / (1 − 1.0025⁻³⁰⁰) = 500 / 0.5272 = 948.36 euros per month
In total, you will repay 284,508 euros, of which 84,508 euros is interest. That is the figure that really matters when comparing offers.
Step by step: how to calculate your mortgage payment
If you prefer an organized method rather than relying on a standalone formula, follow these five steps.
Step 1. Define the loan amount, term and interest rate
Start with realistic figures, not wishful thinking. The price of the property minus your available savings gives you the amount you need to borrow. For the interest rate, use actual offers currently available on the market rather than historical averages.
Step 2. Convert the annual interest rate into a monthly rate
Divide the nominal annual interest rate by 12 and always work with decimals rather than percentages. A 3% rate becomes 0.0025. This is the most common mistake and the one that distorts the final result the most.
Step 3. Apply the formula
Substitute the three variables and calculate the denominator first. That expression, (1 − (1 + i)⁻ⁿ), is where the effect of the repayment term is concentrated, so it is best to calculate it separately before dividing.
Step 4. Check it against the amortization schedule
Create the first few rows and check that the figures match. In our example, during the first month you pay 500 euros in interest and only 448.36 euros towards the principal. If your first row does not match, check step 2 again.
Step 5. Add the associated costs
The mortgage payment is not the only amount leaving your bank account. Home insurance, life insurance, property tax and community fees can add between 100 and 250 euros per month to the actual cost of owning your home.
With these five steps, you now know how to calculate your mortgage payment in any scenario. What changes depending on the type of mortgage is how many times you will need to repeat the calculation.
So much for the theory. At CreditCheck, we focus precisely on the part that the formula does not cover: we combine the information you provide with verified banking data through Open Banking to build a realistic credit profile and show you mortgages and loans that match your repayment capacity. No banking passwords, no access to your account and no commitment. If you want to skip the manual calculations, you can use our Mortgage Payment Calculator to see your monthly payment alongside offers tailored to your profile.
How to calculate a fixed-rate mortgage
With a fixed-rate mortgage, the calculation only needs to be done once and applies throughout the entire life of the loan. The nominal interest rate does not change, so the 948.36 euros from the previous example will be exactly the same in month 1 and month 300.
That predictability comes at a price: lenders usually apply a slightly higher initial rate than they offer on variable-rate mortgages. You are paying for certainty, and for many borrowers that additional cost is worthwhile. When working out how to calculate the payment on a fixed-rate mortgage, the figure you obtain is definitive.
How to calculate a variable-rate mortgage
Here, the calculation is a snapshot rather than the full picture. The interest rate consists of a benchmark index, usually the 12-month Euribor, plus a fixed spread negotiated with the bank. It is reviewed every 6 or 12 months, and your payment changes accordingly.
To understand how to calculate the payment on a variable-rate mortgage, run the calculation using several scenarios. Using the same 200,000 euros over 25 years with a spread of 0.90%:
- Euribor at 1.10% (2.00% interest rate): 847.74 euros per month
- Euribor at 2.10% (3.00% interest rate): 948.36 euros per month
- Euribor at 3.10% (4.00% interest rate): 1,055.55 euros per month
There is a difference of more than 200 euros per month between the best and worst scenarios. The question you need to ask yourself is not what the payment is, but whether you could comfortably afford the highest one. If you are unsure, consider a mixed-rate mortgage: a fixed rate for the first few years followed by a variable rate.
How to calculate it in Excel using the PMT function
If you do not want to deal with exponents, Excel and Google Sheets have the formula built in. It is the fastest way to work out how to calculate your mortgage payment and test different scenarios instantly:
=PMT(3%/12, 25*12, -200000)
The result is 948.36. The three arguments are the monthly interest rate, the number of payments and the loan amount as a negative figure (hence the minus sign: Excel interprets it as money flowing out).
From there, you can build your own amortization schedule with two additional columns: each month's interest is calculated on the outstanding principal, while the rest of the payment reduces the principal. Change one cell and you can see the effect in seconds.
Common mistakes when calculating your mortgage payment
These are the mistakes that most often explain a difference between your calculation and the bank's:
- Using the APR instead of the nominal interest rate. The APR includes fees and will always give you an inflated monthly payment if used in the formula.
- Forgetting to divide the interest rate by 12. Applying the annual nominal rate directly can multiply the payment by more than three.
- Confusing the property price with the amount financed. If you do not subtract your down payment, you are calculating a mortgage you are not actually going to request.
- Ignoring mortgage arrangement costs. Valuation, notary, registration and administrative fees are not included in the monthly payment, but they still affect how much money you need upfront.
- Assuming you will qualify for the best advertised rate. That rate usually requires linked products and a strong credit profile.
The last point is the one that causes the most surprises. You can work out how to calculate your mortgage payment with mathematical precision and still receive a worse offer simply because the lender assesses your income and financial stability differently from how you present them in an application form.