- 1). Enter the following column headers across a horizontal row in Excel: "Rate," "Number of Payments" and "Mortgage Amount."
- 2). Determine the annual interest rate, repayment period and total mortgage balance for the mortgage you are considering. As an example, suppose you are considering a $300,000 mortgage with an 8 percent annual interest rate that requires you to make monthly payments. Also suppose the mortgage requires you to pay off the entire balance in 30 years.
- 3). Enter the monthly interest rate, the total number of payments and the total mortgage amount under the appropriate column headers in Excel. In this example, you would enter 0.7 percent under the "Rate" column, 360 in the "Number of Payments" column and 300000 in the "Mortgage Amount" column. Note that you must enter 8%/12 = 0.7 percent because 8 percent is the annual interest rate and you are required to make monthly payments. Additionally, you must enter 12 x 30 = 360 as the number of payments because you are required to make one payment each month for a total of 30 years.
- 4). Use the "Payment" function in Excel to calculate the monthly mortgage payment. In a blank cell type =PMT( and select the figure in the "Rate" column followed by the figure in the "Number of Payments" column followed by the figure in the "Mortgage Amount" column. Close the parenthesis and press enter. In this case, the payment formula should look like: =PMT(0.7%,360,300000). The formula calculates the monthly mortgage payment as $2,201.
Source...