Sunday, July 19, 2009

Planning to Buy a House Spreadsheet

Home Affordability Calculator: How Much House Can You Afford?

This spreadsheet can help you evaluate a proposed home purchasing plan for reasonability, and answer typical home purchase related questions such as, given your salary/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?

Note: Click on the screenshot below to expand it. The link to download the spreadsheet is at the end of the post.

Planning to buy a home Excel spreadsheet/calculator

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.

Buying a House Spreadsheet: Primary Inputs

The primary inputs (blue cells enclosed in black borders) to the model are:

  • The purchase price under consideration
  • Expected mortgage interest rate (not shown)
  • The total amount you expect to be able to budget for monthly principal, interest, taxes, insurance (PITI), and maintenance.
  • Annual income/salary
  • Current and anticipated savings

Additional inputs (blue cells not enclosed in black borders) may be used to fine-tune other expenses. Not shown, but included in the model, are the inputs for expenses such as:
  • Property tax expenses
  • Insurance and maintenance expenses
  • One-time purchasing costs other than the down payment

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

(Note: Some of the items mentioned below are not shown in the illustration above, but are in the spreadsheet. If you want to follow the example in detail, download the spreadsheet.)

In this illustration, a hypothetical buyer is considering buying a $150,000 home two years from now, and assuming mortgage rates will be about 5.5%. The core calculation begins with his Planned Monthly Housing Expense; given his $50,000 annual salary and current monthly expenses, he expects that he will be able to set aside $1250 each month for housing expenses. The spreadsheet then tests to see whether this is adequate.

Based upon the price of the house, and input in the "expenses" section, the spreadsheet calculates property taxes, insurance and maintenance costs will average $487.50/month. (note: the rationale for starting here is that these are critical expenses over which you have little control.) As a result, the buyer will have ($1250 - 487.50=) $762.50/month available to pay the remaining monthly housing expenses -- mortgage principal and interest; at 5.5%, that would mean a mortgage no larger than $134,292.84.

Therefore, at least a ($150,000 - $134,292.84 =) $15,707.16 down payment would be required. And, based upon the price of the house, and input in the "Estimating One-Time Expenses" section below, the spreadsheet determines that an additional $7,500 would be needed for one-time costs such as closing, moving, etc.

Since the buyer has $10,000 in savings and will save an additional $10,000 over the next two years, the spreadsheet calculates that he will have $20,000 in savings available at the time of purchase.

Finally, the spreadsheet tests to see if the buyer will have enough money to cover the one-time costs, and tests the proposed purchase against various home affordability guidelines.

Buying a House Calculator: Warning Messages

The model can produce the following warning messages:
  • DP less than 20%. I recommend you plan on at least a 20% down payment. The Problem With Low Down Payments discusses the risks of smaller down payments in some detail. Those making smaller down payments will also need to purchase PMI (mortgage insurance); that cost has not been included in these calculations.
  • 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.
  • Too much housing expense. PITI plus maintenance is greater than 28% of your gross salary.
  • Too much total debt. The above plus other debt (credit cards, etc.) is greater than 36% of your gross salary. The model will only flag those with greater than 36% debt. However, in my opinion, the lower you can get your "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. Your initial estimates are likely to be ballpark estimates. As you hone 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.; your estimates can be refined so that ultimately they are precise enough for monthly budgeting purposes.

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 run as high as 4% of the cost of the home per year, or possibly more -- 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.)

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.

Note: This post discusses the Proposed Purchase column in the spreadsheet. See A Borderline Home Buyer for a discussion of the other column.

Related Posts:

100 Years of Housing Price History Robert Shiller's index of housing prices since 1900, & intro to the economics of real estate.
A Borderline Home Buyer: A great benchmark to compare yourself against!
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.
Planning to Buy a House discusses some ways to increase your chances of becoming a successful homeowner.
Inflation calculator/spreadsheet 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.

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 this one.

You can use a site such as Arrange a Room to see how your furniture fits in your new space and to begin to estimate additional expenditures that will be necessary/desirable.

Estimating Annual Home Maintenance Costs addresses those impossible-to-estimate maintenance costs.

Share This Article

Bookmark this on Delicious, To share via Facebook, Twitter, etc. see below.

The picture is from Public Domain Pictures.
Copyright © 2009. Last updated 4/17/2012


  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?

  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.

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

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

  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

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


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.