# 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

## 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)

 Payment Frequency Payments Per Year Annually 1 Semi-Annually 2 Monthly 12 Semi-Monthly 24 Bi-Weekly 26 Weekly 52

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).

 Country Compound Period US 12 Canada 2 UK 1

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.

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.

## 3 thoughts on “How to Calculate Mortgage Payments in Excel”

1. Blaine says:

Why do the values Ii get not match those quoted by my bank?

1. Don’t know. The values I get match my biweekly payment, right down to the cent

2. CG says:

There are slightly more than 52 weeks in a year and therefore more than 26 bi weekly payments in a year. Your bank probably calculates knowing this.