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