How to calculate mortgage payment in excel

Excel offers a built-in function to calculate a loan payment such as a mortgage. With the formula you’ll calculate the monthly payment depending on the total amount of the mortgage, the duration, the annual interest %. To calculate mortgage payment in Excel proceed as follows.

Step 1 – Define the input data for the function

– Total loan amount;
– Annual interest rate;
– Life loan (years);
– Number of payments per year;
– Total number of payments, that is: life loan * number of payments per year.

Step 2 – Select where to place the mortgage payment calculation

Select in which cell write the function;

Step 3 – Write the function

– Write “=” to start the function;
– Write “PMT” and select the function from the list;
– As first argument of the function, divide the interest rate by the number of annual payments;
– As second argument of the function, insert the total number of payments;
– As third argument of the function, insert the total loan amount;
– As fourth argument put a 0;
– Press enter.