I have friend who is getting married in the next two months. While his fiance is busy planning the big white wedding of her dreams with seemingly no budget, my friend wants to move out of their small condo into a house. He’s been busy house hunting for a few months and thought that he had found the house of his dreams. The problem is that even though he makes $80,000 each year from his business, he could not qualify for a loan. His other problem, like many of us, was that he never took the time to use any tools like a mortgage calculator to find out exactly how much house he could afford. Since I helped him figure this out, I thought that I might as well share this with you as well.
Quite honestly, he can afford the house. The problem was that his debt-to-income ratio was way too high. Most of his debt is from open credit lines that he really could afford to pay off from savings including a car note and a personal loan. He thought that with the rates so cheap, he might as well use the bank’s money and hold on to his own for the time being. Not so, my friend.
Anyway, anyone shopping for a house these days has to be armed with vital information. You already know that you need to have a decent credit score, a good down payment, no delinquencies and a stable job history to qualify for a loan. We’re past that. Sometimes though, you go through this process and a number gets spat out at the end of the mortgage shopping experience that leaves you breathless. Instead of having a mortgage broker tell you how much your mortgage will be, why not figure it out yourself before you go shopping?
I really, really like mortgage calculators for this because they take the guess work out of everything and can make allowances for exotic things like adjustable rate mortgages, interest only mortgages, and bi-weekly payments. But, if you are an Excel freak and would prefer to play with the numbers yourself, have I got a formula for you!
If you have a TRADITIONAL mortgage that isn’t like any of the ones that I mentioned above, you will want to become familiar with this formula:
=PMT(INTEREST RATE%/12,MORTGAGE TERM IN YEARS*12,MORTGAGE AMOUNT)
Of course, you won’t paste that into Excel. You need to replace the words in red with actual numbers. If you had a 30 year mortgage for $100,000 at 4% interest, the formula would look like this:
=PMT(4%/12,30*12,100000)
Excel should spit out the number $477.42. Actually, it might show up in red or appear in the negative form because good old Excel considers this to be a debt, which it is. You didn’t know that Excel would be so much fun, did you?
Anyway, have fun with this! Try it out and tell me what you think.
Join the newsletter
Subscribe to get our latest content by email.
Thanks for posting this… very useful.
That’s a really convenient formula! It actually got my mortgage payment almost spot on (would have been more accurate if I actually put in the exact number). Too bad it doesn’t also include my tax payment and HOA which makes my monthly payment shoot up. 🙁
That’s a really convenient formula! It actually got my mortgage payment almost spot on (would have been more accurate if I actually put in the exact number). Too bad it doesn’t also include my tax payment and HOA which makes my monthly payment shoot up. 🙁
I have to work on perfecting that. I thought that this was a neat little trick to how everyone in Excel.
Funny that you wrote about it. I just used this formula to calculate the mortgage rate myself. I always use Excel. The one thing that I trust!
Funny that you wrote about it. I just used this formula to calculate the mortgage rate myself. I always use Excel. The one thing that I trust!
Hey!
This is awesome! i love the story and then the transition on how to fix it. I’m no wear close to buying a house yet because i’m only in high-school. But to know how to calculate my mortgage and keep track of how much i’m spending. Thanks a bunch.
You’re quite welcome. Everyone things that a mortgage is difficult. I try to simplify it with simple mathematics. When that fails, I use a calculator.
Hey!
This is awesome! i love the story and then the transition on how to fix it. I’m no wear close to buying a house yet because i’m only in high-school. But to know how to calculate my mortgage and keep track of how much i’m spending. Thanks a bunch.
You’re quite welcome. Everyone things that a mortgage is difficult. I try to simplify it with simple mathematics. When that fails, I use a calculator.
Useful tool to have. The bf and I just bought a condo, so we’re definitely going to be very acquainted with this formula (hopefully for less then 30 years).
Useful tool to have. The bf and I just bought a condo, so we’re definitely going to be very acquainted with this formula (hopefully for less then 30 years).
I love using the mortgage calculators on my bank’s website. My husband and I own 2 homes and are planning to buy a 3rd – but we need to make sure we can qualify for enough mortgage, in addition to our down payment, to buy the house. Every time we get a raise I hope online and calculate how much more mortgage we can qualify for. It’s motivating!
I love using the mortgage calculators on my bank’s website. My husband and I own 2 homes and are planning to buy a 3rd – but we need to make sure we can qualify for enough mortgage, in addition to our down payment, to buy the house. Every time we get a raise I hope online and calculate how much more mortgage we can qualify for. It’s motivating!
I love using excel for personal finance! Thanks for sharing. It’s important to also add in potential property tax payments for escrow.
I love using excel for personal finance! Thanks for sharing. It’s important to also add in potential property tax payments for escrow.
Yes, definitely always a good idea to crunch your numbers first before taking out a mortgage. Best to air on the cautious side with the debt that you take out in case of any unforeseen circumstances. It also can be rather shocking to find out how much you have to pay the bank for large mortgages and how long it will take to pay off. It’s a good reality check to crunch your numbers.
Yes, definitely always a good idea to crunch your numbers first before taking out a mortgage. Best to air on the cautious side with the debt that you take out in case of any unforeseen circumstances. It also can be rather shocking to find out how much you have to pay the bank for large mortgages and how long it will take to pay off. It’s a good reality check to crunch your numbers.
Hi Sandy,
This is a good article for those people who are not very familiar with functions/formulas in excel. I knew about the formula but you bring up a good point about being armed for the next time I am out looking to purchase a house.
The PMT function got my mortgage payment spot on, right to the penny! At least I know where the bank got their numbers from now!
There’s a pretty complete (and easy to use) Excel spreadsheet which calculates mortgage payments at http://investexcel.net/mortgage-payment-calculator/
It seems to have a complete set of options for changing payment frequencies, mortgage compounding, etc, and gives a complete payment schedule.
I entered my mortgage start date, and the other details like the payment frequency, and it precisely predicted the date at which each payment is taken from my bank account.
That’s a great tool. Thanks for sharing.
I knew I should have payed more attention in statistics class for all of these helpful formulas!