How To Calculate Mortgage Payments in Excel

By Tricia Goss

When you're searching for the perfect new home, it is important to know whether the mortgage payments will be affordable. Before you head to the bank to apply for a home loan, it will be helpful to determine how much home is within your means. Mortgage providers and realty professionals may pressure you to buy a house that will cost more than you can comfortably afford. Use Microsoft Excel to calculate what the mortgage payments would be on the house of your dreams.

Step 1

Start a new, blank workbook in Excel. In Excel 2007, click the "Office" button and click "New"; then click "Microsoft Online." In Excel 2003, go to the "File" menu and click "New"; then go to the "Templates" section of the task pane.

Step 2

Type "mortgage calculator" in the "Search Microsoft Online" box and click "Go." A list of possible templates will appear.

Step 3

Select the "Mortgage Payment Calculator" template and click the "Download" button. The Mortgage Payment Calculator template will open as a new Excel spreadsheet.

Step 4

Enter the mortgage loan amount in cell C7. Enter the interest rate in C8. Type the number of years of the mortgage loan in cell C9. The worksheet will calculate the monthly payment amount, as well as other data, such as the number of payments and equity.

Step 5

Enter up to five different mortgage-loan amounts into columns C through G. This will make it easier to compare options, such as a 15-year mortgage vs. a 30-year mortgage, or different interest rates or loan amounts.