Sunday, July 19, 2009

Planning to Buy a House Calculator

Home Affordability Calculator: How Much House Can You Afford?

This interactive calculator can help you 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 calculator 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 for the total cost of owning your home. It helps you make adequate allowances for housing 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:
  • Mortgage length in years
  • 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 section)

In the Expenses - Principal & Interest section: given the home purchase price, the down payment and the expected 30-year 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 makes an initial estimate that property taxes, insurance and maintenance costs will average approximately $675/month. The buyer can update these estimates as additional information becomes available.

In the Expenses - One-Time section: we see that the planned down payment plus the initial estimate for 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/budget and to various home affordability guidelines.

In the Down Payment section, the buyer is warned that the down payment is 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."

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 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. This is generally resolved by saving more each year, saving for more years, or buying a less expensive home.

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. The buyer is warned that this exceeds his budget.

 Next, the buyer's data is compared to some standard benchmarks. The buyer's housing expense is 33%. He is warned that this is 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. The model will only flag those with greater than 36% debt. However, in my own personal finances, I try to lower that "other debt" -- especially high-interest credit card debt as low as I possibly can.

Note that I am being a bit conservative in those last two calculations. 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. Your home may be the largest investment you ever make; I think it pays to take care of it. However, note that these are just guidelines. For instance, homeowners in high cost areas such as New York City and San Francisco often exceed these guidelines.

Total Cost of Ownership

The final line of the Summary Section is the one-line “Total” section. It suggests that though this buyer might think of this purchase as a one-time commitment to pay $200,000 for a home, it is instructive to think of the total cost of ownership. Using a modified total cost of ownership calculation, we see that before he actually owns this home we expect this buyer will have spent more than $500,000 over a period of 30 years. 
The second number in that section shows that just paying off the $170,000 mortgage (i.e., only principal and interest payments) will cost over $250,000 – because the buyer will pay over $80,000 in interest over the course of the 30-year mortgage. Note that both these “total cost” numbers are historically low. As recently as 2008, when mortgage rates exceeded 6%, the total principal and interest for this mortgage would be more than twice the size of the mortgage amount, and the total cost of ownership would have been approaching $650,000.

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

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/30/2020

Can I Afford This House Excel home affordability calculator /spreadsheet Can I Afford This House Excel home affordability calculator /spreadsheet


Planning to buy a home Excel spreadsheet/calculator


  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.

  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?

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

  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!

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

  9. Note: as of 9/26/20 this spreadsheet is now an interactive calculator. You no longer need to have Excel to use it!


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.