How to Calculate Mortgage Payments in Excel

Like me, you might have used an online mortgage calculator to predict your payments.

But how do those tools come up with your payment amount? Well, armed with Excel, you can easily calculate that magical number

You can download the spreadsheet from the link at the bottom of this post.

But I encourage you to read on for a step-by-step guide to calculating your mortgage payments using your favorite spreadsheet tool!

Step 1: Enter your Mortgage Parameters

First of all, you’ll need to collect the following data

• Annual mortgage rate
• Payment frequency (per year)
• Compound period
• Amortization period (in years)

You can scan your mortgage provider’s website for typical rates and amortization periods.

Here’s a list of typical payment frequencies (and the value you enter into the spreadsheet)

The compound period can be rather confusing – different countries accumulate mortgage interest at different points in the year. US mortgages compound every month,  Canadian mortgages, compound twice a year  and UK mortgages compound once per year. Other jurisdictions may have other compound periods, so double-check if you’re not sure.

Here’s a summary of compound periods (and the value you enter into the spreadsheet).

Enter these parameters into Excel like so.

Here, I’ve formated the loan amount as currency, and the mortgage rate as a percent (right-click on each cell and click Format).

Step 2: Calculate the Interest Rate Per Payment

Next, you’ll need to calculate the interest rate per payment. That’s given by this formula

Type it into Excel, as illustrated in the screengrab below.

Step 3: Calculate the Mortgage Payment

The magical figure – the amount of money per payment – is then calculated with Excel’s PMT function. The full syntax is described here. Briefly, but PMT takes three arguments –

• the interest rate per payment,
• the total number of payments across the lifetime of the mortgage (this is the number of payments per year multiplied by the amortization period),
• and the loan amount.

Assuming that the mortgage parameters are entered into the cells as given in the screengrab above, you’d enter PMT(B10, B7*B6, B4). Do this as illustrated in this screengrab.

And there you have it! I’ve calculated that my bi-weekly payment is $584.06 (I’ve formatted the payment value cell as currency with two decimal places).

Now – that wasn’t so difficult. You can now experiment with different payment frequencies, amortization periods and mortgage rates. Download the complete spreadsheet from the link at the bottom of this post.

If you want a fully-featured spreadsheet to automate the task of calculating payments then download the exclusive Mortgage Payment Calculator!

It offers a complete set of options in an easy-to-use interface, and gives you the total interest paid at the end of your mortgage term. This tool also generates a payment schedule – so you know exactly when your payments will be taken out of your bank account. Get this spreadsheet here.

 Download Excel Spreadsheet to Calculate Your Mortgage Payments