How Much House? A Spreadsheet to Help You Figure It Out

Lu and I have been circling for months on how much house we can actually afford. I'm the anxious type, so my answer is basically "let's get a trailer"; and she's the extravagant type, so her answer is "let's get a mansion". In between, we've considered getting a condo or even just continuing to rent.

We've read all the recommendations on the internet - 30% of household take-home pay, blah blah -, and we've done the mortgage calculators. But none of this has helped us figure out exactly what the ceiling on our house price should be, taking into account not only all the other housing-related costs but also the actual value for your money (i.e., how much interest you pay to the bank in order to have the house).

Then the other weekend I decided to make a spreadsheet and it felt like the fog suddenly lifted. Actually, it gave me an incredible amount of confidence and hope, and so I decided to publish it for people to copy and use.

Take a look here, and feel free to copy it into your own spreadsheet and mess around with the numbers. To do this, do the following:

  1. Create a new spreadsheet in your own google drive
  2. Open the link above in another tab
  3. Click on the top left cell (to the left of A and above 1); this should select the entire sheet
  4. Hit Ctrl-c to copy the whole selection
  5. Click on cell A1 in your new spreadsheet
  6. Hit Ctrl-v to paste the selection

Following is an explanation of what this actually is and how to use it.

Inputs and Outputs

The spreadsheet is a little complicated, but I'll start with the basics and go down from there.

What am I looking at? Basically, for a number of dwelling scenarios (rental, condo, cheap house, expensive house, etc.), this sheet attempts to give you an all-in idea of how much a given scenario will cost you and what you will get (in equity) for that cost. IMPORTANT! "Equity" in this case includes not just the cash that you've put into your home, but also additional cash that you've been able to save in each scenario.

One note up front: It was too complicated to try to work home value appreciation into the total projected equity equation, so that part simply displays cash that you've put into your home equity plus cash that you've put into a savings account or CD earning the interest rate you've indicated in the "fixed quantities". That should give you a good enough idea of your equity, but know that it'll actually be higher, especially the further out your projection goes.

What are the important outputs? I.e., what will this sheet actually tell you? What information will it give you? I've bolded the fields that you'll probably want to be looking at (as well as few of the inputs, which you can ignore until the next section). They are:

  • Monthly Mortgage Payment (F): This is the total principal and interest that you'll pay into your mortgage every month. A site like redfin will usually give you this plus a few of the known monthly costs (see next point) and call it your "estimated monthly payment". Just know that that number will usually be more than this one but less than the "total monthly cost" (below), as I've included a few things that redfin doesn't.
  • Non-Mortgage Monthly Costs (N): This is all of the variable costs associated with living in the given dwelling, including HOA fees, insurance, utilities, parking, storage, misc fees, what you should be saving for maintenance, etc. If there's something I haven't thought of, just add it into the "Misc Fees" column.
  • Total Monthly Housing Costs (O): This is the total cash that you'll spend every month for everything housing related (it's the simple sum of mortgage and non-mortgage costs). This will give you an idea of what kind of cash-flow you'll actually need month-to-month. NOTE: This does include a quantity of savings that's technically optional but strongly recommended (for home maintenance). These maintenance costs will come along, and if you're not saving every month, you'll take a hit you weren't prepared for when they do. SECOND NOTE: This amount can be higher than what you've listed in the inputs as your "Maximum Monthly". If it is, the row will turn red. Obviously that doesn't necessarily mean you can't have this scenario, it just means it will exceed what you said you wanted to pay.
  • Leftover Cash (P): This is the amount of cash you'll be able to contribute to your savings every month after you pay all your housing-related bills (and stash your maintenance savings away). This amount is difference between your max monthly and your total monthly housing costs. It is used to run calculations on how much cash you can continue to save month-to-month in the given scenario, which feeds into your total equity at the end of the period. Now, stashing extra cash in savings for your next house purchase isn't technically necessary, and I also don't think it's "normal", but I think it's a neat way to make sure you're actually reaching your housing goals (or at least building up a sum that you can spend on improvements if you want to) using the 30% rule in it's most literal sense. More on this later.
  • Total Projected Equity (T): This value is the result of a fairly complex calculation that combines your appreciated cash savings with the equity that you've put into your house (both through the down payment and through your monthly mortgage payments). You can think of this as basically "how much cash would I have at the end of the projected period if I sold my house". Now, it's not just important for selling your house; you can also use the equity in your house to take out loans, so it's nice to have a good idea of the full picture.
  • Equity Cost Efficiency: This is a somewhat weird, arbitrary column. What I'm trying to communicate with this is basically how stressful each scenario will be, given the parameters you've set, with 100 being "normal" stress. Note that this is not necessarily "the lower the better". If the number is very low, then you may be missing out on housing opportunities that you could easily achieve. If it's quite high, though, it's likely that the cost outweighs the benefit for the given scenario, making it a generally stressful and not recommended scenario.

Now what are the important inputs? I.e., what parameters can I change to get new information out of the sheet? Most of the inputs are in the "Fixed Quantities" box. However, there are a few fields in the main grid that you can play with as well. We'll get to those in a bit, but the primary inputs are the following:

  • Max Monthly (E17): This is the absolute maximum amount that you're comfortable spending on housing-related costs each month. The easiest way to find this is to take your household's gross annual income and multiply by 0.01833 (this is roughly 0.72 (your after-tax take-home pay) times 0.30 (the recommended proportion of your take-home pay for housing expenses) divided by 12 (to convert from yearly to monthly)). So for example, if you're single and make 100,000 gross annually, your max monthly would be (100,000 0.01833 = 1,833.33, or 100,000 0.72 * 0.30 / 12 = 1,833.33). Note that this number may actually be a little low for you, depending on your other costs. Feel free to bump it up to something that you feel is more realistic. In our case, we have detailed accounting of where our money goes, so we were able to adjust this number based on our comfort level if all other expenses stayed the same, which actually resulted in it being a little higher than the 30% mark.
  • Mortgage Rate (E18): Nothing to explain here. This is your standard yearly mortgage rate. Make sure to look this up based on your credit score, the amount you think you'll be able to put down, and the area you're looking in!
  • Mortgage Term (yr) (E20): Again, not much to explain. This is usually 30 years, but could also be 15, 20 or 25. (If you're looking at adjustable rate mortgages, you may just wanna take a stab at an average rate that you expect to pay and call it a 30 year mortgage.)
  • Cash Available at Close (E22): NOTE: THIS IS NOT YOUR DOWN PAYMENT. It costs money to close a house deal (usually around 3% of the house value), and you're also expected to have a cash reserve that you have to sit on. So this value should be the total value of your disposable cash at the time you expect to close. (If you do want to discount some additional padding for regular life emergencies, don't do that here; instead do it in the "reserve" figure. See below.)
  • Tax Rate (E23): You can look this up on google for the county you're looking at.
  • Savings APY (E24): This number is used to calculate how much your leftover cash grows over the projection period, so try to make it realistic. I've started it at 3.5% because right now money market rates are at 4.9% and likely will be up there for a while. However, conditions will definitely change.
  • Projection Period (mo) (E25): Finally, the number of months you want to project out. 5 years = 60 months, etc.

What are the scenario-level inputs? At the scenario level, the inputs that you can/should mess with are highlighted in green. Most of these are pretty self-explanatory (like "property insurance", "hoa", etc), but here's a brief explanation for the ones that aren't:

  • Reserve (D): How much cash you're going to keep on hand after close. This is a hard-coded value, but banks usually want you to have about 6 months of mortgage payments in reserve at closing. You can figure out how to calculate that. Also, include in this number any additional cash that you might want to keep around for incidentals so you're not 100% after closing.
  • Maintenance Costs (I): This is actually a savings goal, not an expense. It's understood that things in your home such as your roof, refrigerator, water heater, etc, are going to wear out and break down. To prepare for these occasional expenses it's generally recommended that you save around 2% of the home value each year. Remember: even though this is savings, it's not optional! These are real costs and they will come due.
  • Misc Fees (J): This is basically just to cover anything like parking fees, storage fees, mail fees, or literally any other expense that you consider essential to your housing costs.
  • MI (mo) (L): Not technically highlighted in green, but you may want to mess with this anyway. This is mortgage insurance, and it's generally mandatory when you put less than 20% down on your home. The rate seems to vary quite a bit, so maybe look it up in your area.

And that's it! I hope this is as helpful to others as it has been to me. I did say I was gonna elaborate on some of the points from above, but honestly I'm tired and have covid, so I'm gonna go to bed for now. Maybe I'll come back to this!