How to Build a Mortgage Calculator in Excel
Your mortgage payment is dependent on the amount you borrow, the interest rate on the mortgage, and the term of the mortgage. Calculating your monthly mortgage payment is beneficial because it helps you to determine how large a mortgage you can afford. Creating your own mortgage calculator in Microsoft Excel can be especially useful if you have a slow Internet connection and you do not want to wait for a page of amortization numbers to reload each time you adjust the values.
Other People Are Reading
Instructions
-
-
1
Type "Mortgage Amount" into cell A1, "Annual Interest Rate (as a percent)" into A2, Term (in years)" into A3 and "Mortgage Payment" into A4.
-
2
Enter the following equation into B4: =B1*((B2/1200*(1+B2/1200)^(B3*12))/((1+B2/1200)^(B3*12)-1)). B2 is the annual interest rate as a percent so you must divide by 12 to get the monthly interest rate and by 100 to convert the percent to a decimal. When you enter this formula and press "Enter," the cell will contain an error message because you have not yet given it values to calculate.
-
-
3
Right-click cell B4 and select "Format Cells." From the category menu chose "Accounting" and leave the number of decimal places at two.
-
4
Enter the values for the prospective mortgage into cells A1, A2, and A3. The monthly mortgage payment will be displayed in A4. For example, if you enter $100,000 for the mortgage amount, 6 percent for the interest rate and 30 years for the term, you will get a monthly payment of $599.55.
-
5
Change the numbers in A1, A2 and A3 at any time to see how altering the mortgage would affect your monthly payment. For example, if you were to change the interest rate to 5 percent, the monthly payment would drop to $536.82.
-
1
Tips & Warnings
You can copy the formula from B4 into C4, D4, and so on, to compare various scenarios side by side.
