How to Create a Mortgage Calculator With Excel
If you are planning to purchase a home to live in or a building to start a business, it is important for you to know how much you can afford to pay every month in mortgage payments. A monthly mortgage payment basically consists of two parts, an amount that goes towards the principal of the loan and a second amount that goes towards the interest agreed between the two parties. The second part is actually the cost of borrowing money. You can easily calculate monthly mortgage payments using the Excel software from the Microsoft Office suite.
Instructions
-
1
Gather potential loan information
Gather information about your existing or potential loan. This information includes the interest rate, loan terms and the loan amount. If it is a hypothetical loan, you would need to estimate the interest rate. -
2
Launch MS Excel
Launch Microsoft Excel on your computer and open up a blank workbook. -
3
Define Mortgage rate or interest rate
Click cell “A3” in the upper left corner of your workbook and enter the interest rate or the rate of your mortgage as a percentage. For example, if you are looking at a four and a half percent loan, type “4.50” in the cell and press the “Enter” key on the keyboard. -
4
Define your Mortgage period or loan length
Scroll right to the cell B3 and enter the amortization period of your mortgage, also known as loan term, as a number of years. 15 or 30 years are usually used as standard loan terms, but you can also use 20 or 25 years as your loan term. If you want to calculate mortgage payment for a typical 30-year loan term, type 30 in B3 and hit Enter. -
5
Define loan amount
Again scroll right to cell C3 and enter the loan amount in it as a negative number. For example, if you want to look at the cost of a $450000 mortgage, enter “-450000” in the cell and press “Enter”. -
6
Calculate the payment
Navigate to cell D3 and copy this formula =PMT(A3/12,B3*12,C3) and hit “Enter” on the key board. As soon as you press the Enter key, a payment amount will show up in the cell in parenthesis and red. The amount is in red colour because it is showing the amount of money owed. For a realistic estimate of the mortgage payment you will have to pay ever month, add property tax, insurance and other necessary fees to this amount.
