Learn Something
Calculating Your Mortgage Payments with Excel
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.
