How to Make a Mortgage Calculator

Creating a mortgage calculator is an invaluable skill. A mortgage calculator will allow you to quickly determine your monthly payment for different mortgages at various interest rates and under various repayment periods. Mortgage calculators are helpful in determining what you can pay for a house and how your monthly mortgage payments will have an impact on your family's budget. Also, a mortgage calculator can help you quickly compare various mortgage packages to determine how changes in interest rates will have an impact on your monthly payment.

Difficulty: Moderately Easy

Instructions

Things You'll Need:

• Microsoft Excel

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.

Tips & Warnings

• There are numerous online mortgage calculators that can perform the calculations described above. However, you should always double-check these calculations are correct by independently computing your mortgage payment. If you want to see how your monthly payment will change if you change the mortgage amount, interest rate or repayment period, simply change the values in the various column headers. You will not have to re-enter the payment function. Changing the input values will cause the payment formula to automatically re-calculate the mortgage payment amount.

Resources

• Mortgage Calculator