Mortgage Calculator In Excel
Prepare the components of the calculator.
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).
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.
Click on the empty cell beside payment/period (1), and type in =PMT(interest rate/year; total number of payment; – +loan amount;) (2).
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).
Click on the empty cell beside interest cost, and minus the total cost of loan from loan amount (=b10-b4).
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
Further reading: Basic concepts Getting started with Excel Cell References
