Result
- Monthly fee:
- Total cost: (Principal plus interest over years)
- Of which are the interests equivalent to %
Mortgage Calculator
Reviewed by
BS César Delgado
An error occurred while performing the calculations. Please contact us through the form located on our contact page so we can fix it as soon as possible. Thank you very much.
Table of Contents
How to Use the Mortgage Calculator
If you want to use our online simulator, you just need to enter some data into the corresponding forms.
Firstly, indicate the total amount of money requested from the bank to purchase the property.
Secondly, indicate the annual rate that the financial entity will apply to the granted loan, expressed in %.
Thirdly, indicate the number of years stipulated to settle the loan.
Once you have entered all the data into their respective forms, you just need to click “Calculate” for our tool to generate an approximate monthly payment amount in an automated manner.
About Mortgages and Interest
A mortgage is a money loan, where the financial entity has the power to take the property you place as collateral if you could not settle said loan within the anticipated time frame.
The amount of the mortgage can be equal to or even lower than the purchase price of the property in question, however, to that price, you must add a series of interests to determine the total amount to be paid.
It is noteworthy that when requesting a mortgage you have various options to settle the amount with the financial entity from which you have requested it.
How to Calculate Mortgage Interest Step by Step
The first thing required is to know the equation that will allow calculating the monthly interest of the mortgage. To determine the monthly installment that you will have to pay, you must calculate the following equation:
$$M = P \dfrac{ i { \left( 1+i \right) }^{ n } }{ { \left( 1+i \right) }^{ n } -1 }$$
Where the following variables are described:
- M, represents the monthly amount to be settled.
- P, is the total amount of the loan without interests.
- i, is the monthly interest rate, which is calculated by dividing the annual interest rate by 12.
- n, the total number of monthly installments that must be paid to the financial entity.
Once the equation and its variables are defined, you must enter the corresponding data for the total amount of the loan, the monthly interest rate, and the number of installments, in order to calculate the monthly amount you must pay. The referred data can easily be found in your mortgage loan contract or by making an estimate of the same, corroborate the data to ensure its accuracy before generating the calculation.
We will use an example to facilitate the understanding of the information presented, imagine you have a mortgage for €200,000 with a 6% annual interest to be paid over a period of 20 years. This means that in P you must enter €200,000, in i, you will have to enter the monthly interest rate, which would be 0.06 (6/100) divided in turn by 12 (months of the year) where you will get a value of 0.005. In n, you must enter the total number of monthly installments, which would be one for each month in twenty years, which in this case would be 12*20 = 240.
The equation would take the following form:
$$M = 200000 \dfrac{ 0.005 { \left( 1+0.005 \right) }^{ 240 } }{ { \left( 1+0.005 \right) }^{ 240 } -1 }$$
Simplifying the equation:
$$M = 200000 \dfrac{ 0.01655 }{ 2.31020 }$$
Obtaining a result of:
$$M = 1432.77 €$$
Once you know the monthly amount to pay for your mortgage loan, it is important to detail that depending on the type of amortization chosen, some variants may appear.
For example, when the interest rate is variable or mixed, or if an early amortization occurs (in such case, the amount of the debt will change based on the possible added interests and, naturally, as a result of the reduction of the outstanding amount).
In any of the cases presented, the percentage of the installment that represents the interests tends to decrease as the installments are amortized.
How to Calculate Mortgage Installments in Excel
If you need a semi-automatic way to perform the mortgage calculation, Excel is a magnificent option for it, just follow our instructions, and Microsoft's spreadsheet will do the rest:
The first thing you need is to enter the following formula (PMT(6%/12; 12*20; 200.000), you can also adapt the data to individual needs by replacing each figure with a specific cell.
In any case, the 6% represents the annual interest rate which should be replaced by that of the mortgage loan to be calculated, the same with the number 20, which are the years stipulated to return the mortgage and the same with the figure 200,000, which refers to the total amount lent by the bank.
Once you have entered all the required data in the planned cells, they are selected and the enter button is pressed so that the Excel simulator generates the result immediately.
These are all the steps you need to know to calculate a mortgage, the idea of this article was to teach what equation is necessary to calculate it, how to find all the variables to make it effective and, moreover, how to do it in a semi-automatic way using Excel.
This is all you need to know to calculate your mortgage, in this article you have learned what equation you need to calculate it, how to find all the variables to make it effective and, moreover, how to do it in a semi-automatic way using Excel.
We hope our content has been to your liking, and that it has been extremely useful for learning how to calculate mortgage installments by yourself. If you have reached this point with a good taste in your mouth, do not forget to share this post on your social networks, so your contacts can also get to know this community of calculators and simulators for free.
Finally, we would be very grateful if you could communicate with us through the contact page, any potential errors you could find both in the mortgage calculator and in the text, so we can solve them as soon as possible.
