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)

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.

payments1

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

A formula that converts an annual interest rate to an interest rate with a specified compound periodType 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.

payments2

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!

An Excel spreadsheet that caculates mortgage payments and generates a payment schedule

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


2 thoughts on “How to Calculate Mortgage Payments in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 4 + 6 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)