Analysis: Estimating the Monthly Payment

Why does it always seem to come down to money?

Breaking Down the Numbers

Being addicted to numbers and high on analysis, I built a spreadsheet on Google Drive so that we could start playing with the numbers - adding and subtracting options at will to find our what we had to give up to get within our target price range. After setting up the spreadsheet, I quickly realized that second most significant figure impacting our monthly payment was the property tax that needed to be paid. Honestly, I think I knew this before, but it didn't really sink in until I saw the numbers - and just how little some of our removed options impacted our monthly payment!

Let me walk thru my steps to set up our spreadsheet - keep in mind, I am not a CPA, I do not have a degree in finance or accounting or anything mathematical. Not even a science. Any errors are probably glaringly evident to any of those people, but clearly not to me.

Calculating Your Mortgage Payment

I spent some time researching this, trying to figure out the best formula to use in my calculations because I wanted the spreadsheet to do the math based on whatever numbers I would feed into it - and it it needed to recalculate as the numbers changed. I don't like waiting or having to go visit another site to plug numbers into their calculator only to paste them back into my spreadsheet. I want the numbers NOW!

Well, lo and behold, I found I didn't have to learn anything to add this functionality to my spreadsheet - there's already a function built in to Google Sheets that would calculate the monthly payment..(And Microsoft Excel, for that matter.) For someone who doesn't really excel at math, this was a great solution! Behold the beauty of the PMT function!

I chose to setup fields for the number of years for the mortgage, the interest rate and the number of payments per year so that I could make one change and have all of the calculations for all of the house configurations change at once. I was curious how shortening the length of our mortgage might change our payments. The PMT function uses each of those values to calculate the actual payment per month (along with the actual loan amount, of course!).

Estimating Property Taxes

Since I'd already identified the second most significant figure in our monthly payment as our property tax payment, I needed to figure out a method for calculating it in my spreadsheet. A key difficulty in building a new house turned out to be estimating the actually taxes we might wind up paying. In an older house, it is easy to visit the county auditor's web site and look up the tax rates for the past few years - all of this is public information. No math needed. You simply plan on a small increase each year and your calculations offer an acceptable degree of reliability. No surprises - something I really, really like.

However with new construction, it isn't so easy. Since there's no home, there's no history to trend or use as a basis - because there's no house yet. Depending on the history of the development, there might be figure for your lot, but its only going to be based on the value of the land. I was stymied here for a while and tried a number of different approaches - mostly to avoid having to figure out how to actually calculate property taxes using millage rates. It seemed scary to me. And I don't like math. Really, I don't. Finally, one dark and stormy night I gave in. I needed to get reliable figure and I decided it would just learn how to calculate property taxes.

Appraisal Values

After extensive research, I found that most appraisals - the base value used to determine annual property taxes, was less than the sale price of the home. Exactly how much less varied significantly from house to house, but it was almost always less. I only came across one home that was appraised for a value higher than the sale price - but it was for a new home that was just sold in November 2014 (making it very comparable to our situation). In that case - the home sold for $326k, but was appraised for taxes at $328. I decided that in order to have a number that would be close to the actual property taxes we would be paying, I needed to have confidence in the number - and I would rather be over-estimating than under-estimating. Rightly or wrongly, I decided to use the sale price as the basis for my property tax calculations. You may decide otherwise, once everything is said and done, I 'll have to see how close my actual calculations were, but that's going to be a couple of years down the road!

Millage Rates

Millage rates are one of those figures that we really only think about around elections - and only then when there is a levy issue on the ballot. Even still, I never really gave it much thought - but when you're trying to estimate property taxes on a new house it quickly becomes another story.

I found the millage rates for each of the subdivisions we were considering on the country treasurer's website. They post the millage rates for each of the taxation districts within the county. Hopefully you will have similar success finding them as easily as I did! Once I found the list, I had to find the right combination of city/township and school district for each housing development we were considering. In Ohio, this seems to be more difficult than I initially expected. There are many places where the city or township and school district are not same, creating some really unexpected combinations - all of which have an impact on the millage rate for that tax district.

Another factor that goes into this calculation (at least in Ohio) is that the millage rate is only assessed against 35% of the appraised value of the land and any improvements - and there is a further rate reduction for residential properties. Thankfully, the millage rates for the full tax (with no reduction), the residential millage rate (for resident properties) and the commercial rate (for business properties) were all included in the county tax rate publications. They ranged anywhere from 57 to over 90 for the areas we were considering.

My Property Tax Estimation Formula

So far, my formula was shaping up to look like:

Appraisal Value = Sale Amount of Home (assumed)
Taxable Appraisal Amount = Appraisal Value * .35 (only taxed on 35% of appraisal value - at least in Ohio)

But what to do with the millage rate? To apply the millage rate to the Taxable Appraisal Amount, you divide the millage rate by 1,000 ("mill" should have been a dead giveway - but I am a little slow sometimes) and then multiple the result by the Taxable Appraisal Amount.

Annual Property Taxes = (Millage Rate/1,000) * Taxable Appraisal Amount

Now that I had the annual property tax amount, I just divided into twelve monthly payments to estimate the amount that we would need to add to our mortgage to cover our tax payments.

Private Mortgage Insurance (PMI)

PMI is essentially an extra payment that is added to loans when the loan amount is greater than 80% of the value of property. It is a type of insurance that protects the lender should you default on your loan before there is enough equity established in the property to ensure the lender is able to fully recover the loan amount.  It was difficult to find clear information, but the best sources I could find identified this as a percentage of the loan amount - where the actual percentage varies based on the credit score of the borrower.  The range seems to be from .3% to 1.5% of the loan amount annually.

When generating your estimated monthly payment, you can pick a value you think is reflective of your own credit history. I would prefer to over-estimate and be pleasantly surprised later with a smaller payment than under-estimate, so I quasi-randomly chose the value .68. I don't have any science or fact to support that decision, it just seemed to be somewhere in the middle.

Breaking this annual expense into monthly payments that are included in your loan balance due each month is a simple process.

Monthly PMI Payment = (Total Loan Amount * PMI rate)/12

Insurance

Homeowner's insurance is also a must to include for me - granted, its smaller than just about everything else, but why not include it? Just take the annual premium amount and divide it into monthly payments. Easy peasy.

Home Owner's Association (HOA) Fees

Ok, now I know I'm just being ridiculous, but depending on how much the HOA fees are you may want to account for those as well.