Mortgage Payment Calculator

Calculate your mortgage payments with this this free Excel spreadsheet.

You can pick your payment frequency, and generate a schedule giving you the date of every payment

You can also use this tool for US, Canadian and UK mortgages, simply by changing the compounding period.

You also get the total interest paid at the end of term, and the outstanding mortgage balance. By reducing your amortization period, you can also calculate how much interest you’ll save, at the cost of higher payments.

In fact, you’ll get the information you need to make careful decisions about the biggest purchase you’ll probably ever make.

The spreadsheet is easy to use – here’s a screengrab of the data entry screen.

You simply enter the

• loan amount,
• annual mortgage rate,
• amortization period,
• term,
• first payment date,
• payment frequency (you can pick from annual, semi-annual, quarterly, bi-monthly, monthly, semi-monthly, bi-weekly, weekly, accelerated bi-weekly and accelerated weekly payments),
• and the compounding period (you can pick from semi-annual, monthly, quarterly and annual).

After you click the “Calculate Mortgage” button, Excel gives you your payment, the total interest and outstanding balance at the end of the term, and several other items (read this article to learn how this tool calculates the actual mortgage payment value)

You also get a payment schedule, so you can budget appropriately.

The payment schedule gives the interest and principal paid off for each payment, and the outstanding balance.

You’ll find that interest is a large fraction of each payment at the start of your mortgage. However, as time marches on, interest becomes a smaller and smaller part of each payment.

The date of each payment is calculated using the loan start date, the first payment date, and the payment frequency. This employs the VBA DateAdd function.

You’re also given a plot giving the mortgage balance across the entire amortization period.

This mortgage calculator has been cross-checked against the results given by these websites.

• BMO
• TD
• RBC

Small discrepancies may exist; these may because of how the VBA in the spreadsheet calculates interest or performs rounding. Make sure you pick the right compounding period.

• US mortgages use monthly compounding
• Canadian mortgages use semi-annual compounding
• UK mortgages use annual compounding

You can change the compounding period in the spreadsheet, making this tool ideal for US, Canadian and UK mortgages (and others!).

This spreadsheet offers one major advantage above online mortgage calculators – the payment schedule (online calculators generally don’t provide this).

Download Excel Spreadsheet to Calculate Mortgage Payments and Schedule