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.

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).

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.

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

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).

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

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