Mortgage Payment Calculator in Excel

Many people are out buying homes this year as interest rates are at record lows. But figuring out what you can afford can sometimes be a bit challenging. You need to factor in how much of a downpayment you can afford, what your monthly payments will be, and your income. Oftentimes you’ll end up doing multiple what-if scenarios. However, with the free mortgage payment calculator in Excel, you can get a quick snapshot of all the pertinent information to help you determine which house prices are within your range. You can download the template here.

How the calculator works

There are three sets of inputs on the calculator. The first section relates to the mortgage itself — how much of a downpayment you can make, the interest rate that’s available to you today, and how many years long your mortgage will be.

The second section relates to how much income you make as well as how much of your income you want going to cover your mortgage. This is a good way to gauge affordability to ensure that the monthly mortgage payment is within your means. For the monthly income, you’ll want to use after-tax income since this is what will actually be available to you to pay your mortgage payments and other expenses. This is also tied to the worksheet’s conditional formatting. When the % of the monthly mortgage rises above your maximum%, the cells will highlight in red to show that these house prices will be too expensive based on your threshold.

This is an optional section and if you don’t enter it then the spreadsheet simply won’t populate the % of monthly income and there won’t be any conditional formatting applied.

The last section is simply what house price you want to start at, the minimum value that you want to look at. There’s also an area where you can determine at what increments each option should increase by. For instance, if you want to look at a very narrow range, you might put $10,000 to see the different scenarios if the house price increased by $10,000. If you’re looking at a much wider range, you could increment the values by more, such as $50,000 or $100,000.

As you enter values in these fields, the mortgage payment calculator will update its results and show you how the different scenarios look like at the different prices.

The above table will be updated immediately as you make changes to your inputs. Please note the spreadsheet is locked and you only can enter data in the inputs. This is to prevent user error and the possibility that formulas are overwritten.

If you liked this post on the mortgage payment calculator in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.