How to Calculate Monthly Mortgage Payments in Excel

Creating a mortgage payment spreadsheet does not have to be complicated.

Microsoft Excel contains a powerful finance solver tool. Using it, you can easily compute potential monthly mortgage payments, and also conduct what-if modeling. By setting up a simple spreadsheet to keep track of the variables that go into calculating a mortgage--the amortization, interest rate and amount--you can understand how various changes would affect your monthly costs. Spending just a few minutes with Excel can make it easier for you to understand exactly what you can afford.

Open Excel

Open Microsoft Excel with a blank spreadsheet.

Define Loan Amount

Enter your desired loan amount as a negative number in cell A1 in the upper left corner of the spreadsheet, then press the "Enter" key. For instance, if you would like to look at the cost of a $300,000 mortgage, you would enter "-300000" and then press the "Enter" key. Remember to include not only the amount that you will be borrowing for the purchase or refinance, but also any closing costs that you intend to finance.

Define Mortgage Rate

Scroll down to cell A2 and in that cell, enter the rate of your mortgage as a percentage. For instance, if you are looking at a five-and-one-quarter-percent loan, you should enter "5.25%" and then hit the "Enter" key. When you are solving for mortgage payments, use the mortgage's actual interest rate instead of its APR. Unless you plan on paying a number of up-front discount points, the actual interest rate should be lower than the APR.

Define Mortgage Length

Input your mortgage's amortization period as a number of years into cell A3. For a typical 30-year mortgage, you would key in "30" and press "Enter."

Solve for the Payment

Navigate to cell A5, and enter this exact formula:

=PMT(A2/12,A3*12,A1)

Then hit the "Enter" key.

Do What-If Models

Engage in what-if modeling by changing any of the numbers in cells A1, A2 and A3. The monthly payment that Excel calculates in cell A5 will automatically update. This can be useful in analyzing the impact of paying discount points, which would require an increased loan amount in exchange for a lower interest rate. You can also use this to see what different houses would cost.

References

• Microsoft Excel
• Microsoft Office: Plan payments and savings with Excel