Sunday, July 19, 2009

Planning to Buy a House Calculator

Home Affordability Calculator: How Much House Can You Afford?


This calculator can help you evaluate the reasonability of your plan to buy a house, and answer typical home purchase related questions such as, given your assets and income:
  • What price can I afford to pay for a house?
  • What size home mortgage loan can I qualify for? How much will I be able to borrow?
  • When will I be able to afford to buy?

The spreadsheet will also help you overcome some of the issues raised in The Disadvantages of Buying a House and Planning to Buy a House by helping you to budget adequately for typical expenses and at the same time keep those expenses within standard established income & debt-load guidelines. Links at the bottom of the post provide additional help in estimating those expenses.

The Buying a Home Calculator



Notes: You can use the arrows and sliders on the right side and bottom of the calculator to scroll up and down, and left and right. Enter data only in the blue cells. On some phones you may need to double click to enter data. On some computers, you may have to enter some fields more than once for it to "take." Please leave a comment if you are having problems.

Buying a House Calculator: Primary Inputs

The first section contains the primary inputs to the model. They are entered directly on your screen in the blue cells enclosed in black borders. They include:

  • Annual gross salary
  • Current and anticipated savings
  • Your planned monthly housing expense -the total amount you expect to be able to budget for monthly principal, interest, taxes, insurance (PITI), plus maintenance expense... PITIME!
  • The purchase price under consideration
  • Your planned down payment
  • Expected mortgage interest rate

Additional inputs (blue cells not enclosed in black borders). You may have to scroll down to see some of these:
  • Property tax expenses
  • Insurance and maintenance expenses
  • One-time purchasing costs other than the down payment
Notes: Update the default amounts and percentages with numbers appropriate for your situation. Ignore the warning and error messages until you have finished entering your data. If you are reading on a mobile device try landscape mode to see if you prefer that.

How the Calculator Works When Planning to Buy a Home: An Example

In this illustration, a hypothetical buyer is considering buying a $200,000 home two years from now, is  planning to make a down payment of $30,000, and is expecting to get a 3% mortgage for the balance. Given his $50,000 annual salary and current monthly expenses, he expects that he will be able to set aside $1000 each month for housing expenses. The spreadsheet then helps the buyer flesh out and test this plan.


The Expenses Section (you may have to scroll down to see this)

In the Expenses - Principal & Interest section: given the home purchase price, the down payment and the expected mortgage interest rate of 3.0% on the $170,000 mortgage balance, the calculator determines the monthly principal and interest payments of $716.73 per month.

In the Expenses - Taxes, Insurance & maintenance expenses section: based upon the price of the house, and input in that section, the spreadsheet estimates property taxes, insurance and maintenance costs will average approximately $675/month.

In the Expenses - One-Time section: determines that the planned down payment plus the estimated miscellaneous one-time costs will total $40,000.

The Summary Results Section

This section reviews the buyer's proposed purchase, comparing one-time and on-going costs to his plan/budge and to various home affordability guidelines.

Since the buyer has $12,500 in savings and will save an additional $15,000 over the next two years, the spreadsheet calculates that he will have $27,500 in savings available at the time of purchase, plus $10,000 contributed by the buyer's rich uncle (note: gift down payments are subject to certain restrictions). The buyer is warned that the down payment is less than 20%. The next message warns that the $37,500 he will have at the time of purchase is $2,500 less than the anticipated need to cover the down payment plus the additional one-time expenses.

Next is the on-going costs section of Summary Results. There we see that monthly PITIME costs (PITI plus anticipated maintenance expenses) total $1391.73 - more than the buyer had budgeted/planned.

 Finally, the buyer's data is compared to some standard benchmarks. The buyer's housing expense is 33% - more than the 28% of gross salary that is the normal standard. The buyer's housing expenses combined with his other debt result in a total debt load of 41% - more than the 36% benchmark typically used by mortgage companies. Note that I am being a bit conservative here. I have included all of the buyer's anticipated maintenance expenses in housing costs; most banks and mortgage companies only include maintenance expenses for which you are contractually obligated such as homeowners' association charges. Also, note that these are just guidelines. For instance, homeowners in high cost areas such as New York City and San Francisco routinely exceed these guidelines.

Buying a House Calculator: Warning Messages

The model can produce the following warning messages:

  • DP less than 20%. The Problem With Low Down Payments discusses the risks of down payments smaller than 20% in some detail. Those making smaller down payments will generally also need to purchase PMI (mortgage insurance); that cost has not been included in these calculations; if needed you can add it to the "taxes, insurance & maintenance expense." 
  • Not enough savings to cover one-time expenses. This is generally resolved by saving more each year, saving for more years, or buying a less expensive home.
  • PITIME exceeds plan. Calculated PITI plus maintenance expense is greater than the "Planned monthly housing expense" established in the top section.
  • Too much housing expense. PITIME is greater than 28% of gross salary.
  • Too much total debt. The above plus other debt (credit cards, etc.) is greater than 36% of gross salary. The model will only flag those with greater than 36% debt. However, in my opinion, the lower you can get that "other debt" -- especially credit card debt -- the better.


Fine-Tuning Monthly Expense Estimates


The model can be used to begin planning years before the actual purchase. Initial estimates are likely to be ballpark estimates. As you home in on specific neighborhoods and homes, your realtor will be able to help you more accurately estimate your mortgage interest rate, property tax rates, etc. In some cases you can even get historical utility expenses. Your estimates can be refined so that ultimately they are precise enough for monthly budgeting purposes. Some of the links at the end of the post will also help you refine your estimates.

Note: Some models do not include maintenance as part of monthly housing expense, or only include it for condominiums and co-ops; I think this is a mistake. Your home may be the largest investment you ever make; it's important that you take care of it. Ongoing maintenance can sometimes run as high as 4% of the cost of the home per year -- especially for older homes.

Estimating One-Time Expenses


Your realtor will be able to help you arrive at an accurate estimate of closing costs.

It's helpful to mentally walk through every room to identify needed/wanted additional furniture and fixtures: bedrooms (furniture for a 2nd bedroom?), LR (a new couch/chair?), kitchen (small appliances, dishes, pots & pans, ...), etc. Don't forget the walls (draperies, paintings?), floors (throw rugs?) and ceiling (new fixtures?). Then there's the garage (shelving?, toolkit for minor repairs & maintenance), and outside (lawnmower, rakes, snow blower?).

If you are a first time homebuyer, the list of things you're likely to want or need will astound you; you'll probably need to spread these purchases over a number of months, or years. To avoid putting your finances under stress, it is critical that you consider these costs, and budget for them. (For links to help with estimating the one-time expenses, see "Related Posts" below.)


Related Posts:

Planning to Buy a House discusses some ways to increase your chances of becoming a successful homeowner.
The Risks & Disadvantages of Buying a House : The pros are well advertised, but there are cons as well.
The Risks & Disadvantages of Low (3%, 5%) & No Down Payment Mortgages: The risks of 0% - 10% down mortgages.
100 Years of Housing Price History Robert Shiller's index of housing prices since 1900, & intro to the economics of real estate.
The Observations Inflation Calculator converts prices to equivalent prices in another year.


Links for Help in Estimating Expenses


I always start with an overall action plan for my move that covers everything that needs to be done. Unfortunately, every step in your move has the potential to generate expenses. If you Google "Moving Checklist," you'll see links like this.
MyMove can help you with your checklist and virtually anything else that has to do with your move. 
I estimate the cost of the physical move starting with an estimate from a full service mover such as Allied . You can get a ballpark estimate for the lower end/"do it yourself" spectrum of moving expenses from sites such as U-Pack.
Arrange a Room can help you see how your furniture fits in your new space and begin to estimate additional expenditures that will be necessary/desirable.
This article and this article address those impossible-to-estimate maintenance costs.

Share This Article

To share via Facebook, Twitter, Pinterest, etc., see below except on mobile devices (where you share in the normal way).

Copyright © 2009. Last updated 9/19/2020


Archives:

Planning to buy a home Excel spreadsheet/calculator

12 comments:

  1. Great insights on the purchasing a house! I tried to load your calculator but Google Docs is only giving me permissions to view an HTML version of the spreadsheet. I would like to view your equations, download the model, and enter my own assumptions. Is there a setting in Google Docs you can set on this spreadsheet to make that accessible?

    ReplyDelete
  2. Michael,
    Thanks for the heads up. My intent was to allow readers to download the model so that they could make the changes you mention. Not sure where I went wrong.

    Let me try a few things this weekend to see if I can give you access. I may need some help testing the changes.

    ReplyDelete
  3. Thanks, Al! I'll check back later this weekend.

    ReplyDelete
  4. I think I was able to fix it. Please let me know if you're still having trouble getting access.

    ReplyDelete
  5. Your first question is indeed relevant all throughout the purchase of your house. Your financial state would indeed dictate what size of lot, size of the house, and the furniture you would be able to purchase for your home. In addition, in buying a house, proper advice is also part of having a sound decision. A soon homeowner, could exhaust all his/her possible advisors, family, friends and even workmates. But, another option is, hiring a professional lawyer that could help explain documents (i.e. title fees, taxes, mortgages, insurance, and surveyor certificates.

    Kathleen Salazar

    ReplyDelete
    Replies
    1. I think it's a given that one should get professional help. That includes at least considering a lawyer.

      Delete
  6. I'm really enjoying your blog; thanks for the wealth of info and links.

    One link I was not able to download was to the "Planning to Buy a House Excel spreadsheet above. I got the following error message: "Sorry, the file you have requested does not exist." Is it still live?

    ReplyDelete
  7. I wasn't able to pull up the spreadsheet. Is it still available? Thanks!

    ReplyDelete
  8. Link to Spreadsheet
    Here's the link to download the Planning to Buy a House Excel spreadsheet. If you have any problems accessing or using the model, see this link. <<I cannot access this link, please update as I wuld love to make use of it! Thanks!

    ReplyDelete
    Replies
    1. If you're still interested, I think I just fixed it. Let me know if it works.

      Delete

No spam, please! Comment spam will not be published. See comment guidelines here.
Sorry, but I can no longer accept anonymous comments. They're 99% spam.