How to build your own mortgage payment calculator in excel

Excel spreadsheets (.xls)

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to for professional help and Excel Tutorials

One of the more common questions that we have received lately is how to build mortgage calculators in excel. It’s actually fairly easy to do but it depends on how much complexity you add of course. Using the “PMT” excel function will help you but what if you want to test the mortgage payment amount depending on the frequency of your payments, on the amount you will borrow, on the rate and of course on the length of your mortgage? it’s all possible. In this spreadsheet (link below to download it for free), I used nested if statements in order to determine the number of periods based off of the frequency of payments and the rate to use. It is fairly easy to go through, here is an image:

Download our free excel mortgage payment calculator spreadsheet here

All numbers in the lighter color are those that you must enter:

-House Price
-Down payment
-Mortgage time length
-Fixed Rate
-Payment frequency (since we used “if” conditions, the frequency must be entered as described on the right)

Depending on the frequency, I will determine the number of periods. Why? Because if you pay every month, you will not be charged interest on the capital that you paid back the following week. That is why it makes such a big difference to pay a mortgage bi-weekly compared with monthly (that and the fact that you actually have more payments because of the 30 or 31 days per month!).

Download our free excel mortgage payment calculator spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


7 Feedbacks on "How to build your own mortgage payment calculator in excel"

Carnival of Personal Finance : Where does the money go?

[…] from Experiglot presents How to build your own mortgage payment calculator in excel, and says, “Are you interested in building your own […]

Mortgage qualification

First of all I would like to say great blog! I had a quick question in which I’d like to ask
if you don’t mind. I was interested to know how you center yourself and clear your head prior to writing.
I’ve had difficulty clearing my mind in getting my ideas out.
I do enjoy writing but it just seems like the first 10 to
15 minutes are wasted simply just trying to figure out how to begin.

Any suggestions or tips? Many thanks!


I became enthusiastic about goji about 8 years ago when his daughter, then 8 years old, was diagnosed with
Type 1 all forms of diabetes and a teacher suggested that a fruitaceutical beverage containing goji would be
good for the woman. Garrigus researched goji
just so that you can fend him off, but while doing thus he became assured himself in the
fruit’s healthful properties and began planting 4 acres of the bushy
trees from seed imported from China.

unsecured loans instant decision

I blog quite often and I genuinely thank you for your information. This article has really peaked my interest.
I will bookmark your website and keep checking for new details about once per
week. I subscribed to your Feed too.

what is my classic car worth

This is a good tip especially to those fresh to the blogosphere.

Short but very accurate info_ Thank you for sharing this one.
A must read article!

Professor Christopher J. Berry

In that case, you are only playing with fire.

homebase discount codes

homebase discount codes

How to build your own mortgage payment calculator in excel | Experiments in Finance


Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.