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

Toggle

## 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, and type in =B6*B7.

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

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, and multiply payment/period with the total number of payments (=B9*B8).

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