How to calculate a mortgage loan in Excel?

Excel is a very useful and versatile calculation tool that allows numerical simulations through formulas and functions. Excel is used to calculate credit amortizations, which becomes a very useful instrument for the client since he will know, according to the data entered in the spreadsheet, how much he will have to pay each month. But how to calculate a mortgage loan in Excel?


How to calculate a mortgage loan in Excel?

mortgage loan in excel

Calculating a mortgage loan in Excel is very useful as it will help you compare the characteristics of the loans of several banks, allowing you to modify the values ​​and always obtaining the payment of the fee corresponding to the variables you indicate. Making the calculations in an Excel sheet you will know exactly how much will be the value of the installment that you must pay each month, according to the interest rate, credit amount and term.

In the case of the French amortization system the fee is fixed, so that every month you will pay the same. In this way, by doing the simulation in Excel we can know the value of a mortgage loan installment with only three variables, which we can change at will, obtaining as a result the fee according to how you modify the variables. It is a very simple and practical calculator.

To calculate a mortgage loan in Excel with the French amortization system, the first thing you should do is determine the variables involved:

  • Loan Value
  • Interest
  • Term (in years)


Payment formula

Payment formula

With these three variables , the Value of the Quota is obtained by applying the PAYMENT formula (financial formula). In this case, of the 5 values ​​requested in the formula, only the three variables mentioned correspond, the other two equals 0:

  • For the first variable, select the interest rate cell: as the term we place is in years, after selecting this cell we must divide it by 12.
  • The second variable is the cell with the term: as the term we place is annual, we select the cell and multiply by 12.
  • The third is the initial value of the loan.

Suppose we want to calculate a mortgage loan in Excel of $ 10,000,000, at an annual interest rate of 3% and within 20 years. We apply the PAYMENT formula to obtain the value of the monthly installment to be paid as follows:

With this formula we obtain the fixed payment of the monthly installments that must be paid for a mortgage loan that has these characteristics. When the formula is applied, you can change the variables at will and see what the result of the fee would be if you modified the amount, interest rate or term.

Knowing how to calculate a mortgage loan in Excel will allow you to create a simulator in which you can manage and change the variables to determine different payment scenarios, either to choose the term in which it is more convenient to pay and according to the amount and rate of interest of the bank.