When you buy a house, you’re committing to make monthly payments for a substantial period of time – at least a couple of years, possibly as long as thirty years. You don’t want to be surprised at all by the size of that monthly payment. When you receive a copy of your closing documents shortly before signing, you’ll have a fair idea[sup]*[/sup] of what it’s going to be, but at that point you’ve already selected a house, a lender, a downpayment size, an interest rate, and a payback period, and you’re just about locked into the purchase. Ideally, you would be able to calculate your own payment ahead of time and use it as a decision-making tool to determine all those other parameters.
You can in fact do this with Excel’s PMT() function. This lets you calculate a monthly payment on the basis of loan amount, interest rate, and payback period. However, this is only two components (principal and interest) of your monthly payment.
[sup]*[/sup] The third component of your monthly payment is property tax. Your lender takes this chunk of your monthly payment and stashes it in an escrow account, where it then gets used to make the annual/biannual property tax payments to your city. Many cities limit the rate at which property taxes can rise in response to increasing property values; this way people aren’t forced out of their homes by enormous property tax payments during a real estate bubble. The tricky part is that the taxable value resets when the property changes hands. So for example, if someone’s property value increased from $150,000 to $300,000 over the past five years, their annual property tax may only have increased from $3000 up to $3500. If that owner stayed in the house for another decade they might see the property tax slowly ramp up to the full $6000 associated with that $300K value. But when you actually buy the house for $300,000 today, the property tax calculation resets – and you’ll start owing $6000 per year right away. This will come as a shock if you were expecting the property tax to be only $3500. So when you’re calculating your monthly payment in Excel, you need to find out what the city’s millage rate is, and calculate what your tax will be based on that and your actual purchase price of the house. Divide by 12, and now you’ve got the property tax portion of your monthly payment. You won’t be paying this much right after you close, but as soon as the city reassesses your property (typically once per year), you’ll get a reassessment notice, and your lender will notify you of a pending escrow shortfall –and tell you to start making larger monthly payments. If you did your math right, this new payment will be pretty much the number you calculated before you even bought the house.
Note that in addition to the loan closing costs specified by the lender when you’re phone-shopping for a loan (loan application fee, title insurance, etc.), there are two other chunks of cash you’ll need to bring to closing that usually aren’t included in the closing cost number they give you over the phone:
-if the property tax bill is about to come due soon after you purchase the house, then the seller’s escrow account will be just about full – and he’s going to take that with him. You’ll need to bring an equal amount of cash to fill up the escrow account again so that it will be ready to make the property tax payment with your money instead of his. Depending on the amount of property tax that will be due, and when it’s going to come due, this chunk of cash could be several thousand dollars.
-at least one month’s interest. The first monthly payment won’t be made for at least one month, so you’ll be required to pay upfront for the interest that will accrue during that period.
With a well-built spreadsheet, you can go in and tweak the various numbers – purchase price, downpayment, interest, payback period, etc – and see how they affect your monthly payment, and how much money you need to bring to closing - and now you can know what you’re getting into well before you arrive to sign the paperwork. Now you’ve got a powerful tool for helping you figure out which house you can buy, which lender you ought to use, what payback period you can work with, and so on.
If you want to be really thorough, you’ll also be tempted to factor in the income tax deduction you’ll get for the interest and property tax you pay every year. My advice? Don’t factor that into your purchase decision. I think there’s a tendency for folks to push themselves into the biggest payment they can possibly afford, instead of one that their finances can comfortably handle. If you’re trying to factor income tax deductions in to your purchase decision, that’s a sign that maybe you’re overreaching, and not leaving any cushion in the rest of your budget. Instead, let that income tax deduction just serve as a pleasant stress reliever every spring instead of a critically important line item in your budget.