How to Build a Mortgage Calculator
- 1). Create a new spreadsheet. When you open the spreadsheet program, a new sheet should be available. If not, click "File" and the "Open" in the navigation bar at the top of the spreadsheet program window.
- 2). Label cell A1 as the amount of the mortgage, cell A2 as mortgage interest rate, cell A3 as the number of months that make up the term of the mortgage, and cell A4 as the monthly mortgage payment.
- 3). Input the monthly mortgage payment formula "= PMT (B2/12/100, B3, B1)" in cell B4 of the spreadsheet.
- 4). Change the cell formats. Highlight the columns of the spreadsheet, click "Format," then "Cells."
- 5). Change the number formatting. Click on the "Number" tab, then "Accounting" and alter the "Decimal" places box to 2 so the numbers appear as a dollars and cents format. Click on the "None" option in "Symbol," then "OK" to remove the dollar sign symbol.
- 6). Calculate the mortgage payment. Input the mortgage amount into cell B1 without using a dollar sign or comma. For example, if the mortgage amount is $100,000, input the number into cell B1 as 100000. Input the interest rate for the mortgage in cell B2 without the percent sign, but with the decimal point. Type the term of the mortgage in months in cell B3 (i.e., 180 months for a 15-year mortgage). Press "Enter," and the mortgage payment appears in cell A4.
Source...