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.

Prepare the components of the calculator.

components

Display the data you already loan. How much is being loaned (loan amount) (1), the interest you are charging per year (interest rate/year) (2), and how long it would take to pay back the loan (loan length) (3). How many times the client will pay per year (4).

display data

Note: You could right click on empty cells before stating the values, and choose format cells, choose currency, and percent if you have any issues creating the mortgage calculator.

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

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

pmt function

Note: If you do not add minus in front of the loan amount, you would get negative numbers as result.

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

total cost

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

interest cost

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


Template

You can download the Template here – Download