How to Create a Mortgage Calculator in Excel

When creating a mortgage calculator, it is important to consider everything that would make the mortgage profitable for your company. We are going to create a mortgage calculator together in a step-by-step format. We will not just create it, but also calculate it.

Data preparation

Prepare the components of the calculator.

mortgage calculator components

Display the data you already have. How much is being loaned (loan amount) (1), the interest rate per year (interest rate/year) (2), and the length of time it will take to repay the loan (loan length) (3). How many times will the client pay per year (4).

mortgage calculator display data

You could right-click on empty cells before stating the values, choose Format Cells, choose Currency, and Percent if you have any issues creating the mortgage calculator.

Preparing the mortgage formula

Click on the empty cell beside the total number of payments (1), and type in =b6*b7 (2), and then press enter.

mortgage calculator total payments


Click on the empty cell beside payment/period (1), and type in =PMT(interest rate/year, total number of payment, -loan amount) (2).

mortgage calculator pmt function

If you do not add a minus in front of the loan amount, you will get negative numbers as a result.

Click on the empty cell beside the total cost of the loan (1), and multiply payment/period with the total number of payments (=b9*b8) (2).

mortgage calculate total cost


Click on the empty cell beside interest cost, and subtract the total cost of the loan from the loan amount (=b10-b4).

mortgage calculator interest cost

In conclusion, we now know how much profit we will make if we borrow this amount of money from the client and how much the client would pay every month.

You can download a free Mortgage Calculator template here

Further reading:
How to subtract in Excel
How to create a household budget
How to calculate emi
Loan amortization in Excel