Make a Buy vs Rent calculator spreadsheet

Is it better to buy or rent a house? Advice on this problem comes in all shapes and sizes, from the dogmatic idea that homeownership is the American dream, to some more nuanced methods like calculating the price to rent ratio. What would you do if you found a great house and are deciding whether to buy it or keep on renting? *Note: We’ve updated this analysis and spreadsheet, given changes in the economics of housing due to the Tax Cuts and Jobs Act from late 2017. For the latest, please see A 2019 update of our House Rent vs Buy IRR Spreadsheet

In our last post, Is Medical School Worth It? , we introduced the idea of the Internal Rate of Return (IRR) by evaluating whether investing the time and money to go to med school was worth it. In that post, we evaluated the total cash outflows (medical school tuition and lost wages) as well as the cash inflows (higher salary after graduating from medical school and finishing residency) to determine the internal rate of return of the money and time invested in medical school. Comparing this internal rate of return to other investments gives us a sense of whether it is worth it to go to med school (the answer is yes, it is typically worth it).

In this post, we’ll build a spreadsheet to calculate the Internal Rate of Return on the cash outflows and inflows from buying a house instead of renting. If you don’t want to build your own spreadsheet, just check out the very user-friendly rent vs. buy calculator from the New York Times. However, the beauty of making your own spreadsheet is that you can customize it, gain a stronger understanding of all the variables required in making this decision,  and build upon your spreadsheet problem solving skills!

Spreadsheet Inputs

There will be many inputs, because there are a lot of different costs to owning a home. From a big picture perspective, the cash outflows are the upfront down payment, as well as any costs of homeownership that you wouldn’t incur while renting (property taxes, insurance, maintenance, etc). The cash inflows to owning a home are any savings or other benefits (rent paid, mortgage interest tax deduction, house price appreciation, etc). We’ll assume the home is sold down the line.

Costs of Homeownership (Cash outflows)

Home purchase price: The amount you are thinking of buying the house for.

Percentage down payment: Typically, people put 20% down for the house and take out a mortgage for the rest.

Purchase closing costs: The amount the buyer needs to pay for various costs like escrow, appraisals, lawyers, title insurance, etc. Redfin appears to estimate this to be around 2-5%

Mortgage rate: The interest rate the bank charges you to borrow to buy the house. Bankrate keeps track of industry average mortgage rates. We are going to ignore mortgage principal paid for now as it is effectively like paying yourself.

Property tax rate: Varies by state – usually you can get a sense of your property tax rate by going to your local county assessor’s website.

Home insurance costs: This is hard to know ahead of time and might be affected by whether you live in flood-prone or fire-prone areas. This site has a list of average insurance costs by state to get a rough estimate.

Maintenance costs and other costs (HOA fee, etc): Let’s save some room in our spreadsheet and lump the rest of these costs together. This website recommends about $1 per square foot per year.

Benefits of Homeownership (Cash inflows)

How much would the house rent for?: By buying the house, you save on the rent payments, so those are effectively cash inflows (money saved is money earned)

Marginal Income tax rate: We’ll use the tax rate to figure out the benefit of the taxes saved from deducting the mortgage interest paid and the property tax paid (as long as you don’ t fall under AMT) from your income taxes. Tax brackets can be found in our How to Estimate Taxes with Spreadsheets post

Average annual house price appreciation: This is probably the most important variable in the analysis, and ironically it is the most difficult to estimate. You can always look to history, or assume house prices will increase in line with inflation, but it’s really hard to say with any confidence what home prices will do over the years that you own your house.

Other Inputs

Inflation: Costs like insurance, maintenance, and the rent you’d pay typically rise with inflation.

Time spent in the house: For now, let’s keep the analysis simple and assume you stay in the house 10 years. We can try adding something fancy to make this a variable later.

Sale closing costs: When selling, you typically have to pay the expenses for the selling agent and the buying agent. This site estimates around 7% in total selling costs.

Spreadsheet Logic

Now that we have painstakingly set up our spreadsheet with the input variables, we can start putting together our yearly cash flow model. We will calculate each cash outflow and inflow for each year using our inputs.

Down payment: This occurs in year 0 (today), and would be equal to our Purchase price ($350,000, cell B6) multiplied by the Percentage down payment (20%, cell B7).  Note this is a negative number because it is a cash outflow.

Purchase closing costs: This cash outflow happens with the purchase in Year 0, and is equal to your home purchase price ($350,000,cell B6) multiplied  by closing costs (2.50%, cell B8). Similar to the down payment above, this will also be a negative number.

Mortgage interest paid: The amount borrowed is -(1-Percentage down payment)*(Home purchase price). And this multiplied by the mortgage interest rate (cell B9) would be the annual interest paid. This amount would be paid annually from years 1-10. Don’t forget to use the fixed reference for functions when you paste the formula for mortgage interest over to years 2-10.

Property tax paid: This will be an annual cost of (Home purchase price) * (Property tax rate). For years 2-10, we will be increasing the amount by the inflation rate. The formula will be (1 + inflation rate) * previous year’s property tax paid.

Home insurance, maintenance: These just go straight into year 1, and then we do a similar (1 + inflation)*previous year calculation to get years 2-10.

Here’s what we have so far after entering our cash outflow assumptions:

Here’s how it looks with the formulas expanded (press Ctrl + ~ to see formulas):

Now let’s enter in our cash inflow assumptions:

Annual rent saved: For year 1, this will be Monthly rent saved (cell B15) times 12. This will be a positive number because it is money we didn’t have to pay because we bought the house and have a place to live. We’ll use the same inflation growth calculation to fill in years 2-10.

Mortgage interest tax deduction: The IRS allows you to deduct mortgage interest paid from your income taxes. The benefit of this will be the marginal tax rate (cell B16) multiplied by the amount of mortgage interest paid (cells C29:L29), times (-1) because the benefit will be positive.

Property tax deduction: This is similar to the mortgage interest deduction: (cell B16) * (cells C30:L30) * (-1)

Gain on home sale: We assume here the house appreciates and we sell it in year 10. We’ll track the home value in row 24. In year 0, the home value will equal input cell B6. Then we will inflate the value each year by (previous year value) * (1+ home price appreciation (cell B17)). The gain on the home sale is the home value in year 10 (Cell L24) minus the price paid (Cell B6).

Closing costs: Sale closing costs assumption (Cell B22) * Home sale price in year 10 (Cell L24) * (-1)

Down payment return: Okay, we cheated a little for simplicity and didn’t model in principal and interest for the mortgage. We assumed our mortgage was interest only so the principal balance of the mortgage is unchanged after 10 years. Effectively, you get your down payment back plus the appreciation on the home when you sell. This is just the negative of the initial down payment (Cell B27).

Final Output

Now that we have all the relevant cash inflows and outflows for all 10 years, we can sum them up for each year and calculate the IRR of the cash flow stream to find out what the Internal Rate of Return from buying the house is.

For our hypothetical example, we got an IRR of 8% from buying a house, which seems pretty good compared to other investments available these days.

Finally, we use a data table to display the IRR of buying a house under a variety of different home prices and alternative monthly rent costs. It looks like if you’re planning to stick around for 10 years, homeownership is generally a pretty good deal:

If you’re thinking about buying a home and want to improve your spreadsheet skills, try to build this spreadsheet yourself. Here’s our version: Home: Buy or Rent IRR Analysis

You can even improve upon our example and make the model closer to real life by adding a  mortgage amortization schedule and making the number of years you stay in the house a variable. We’ve done it in the following spreadsheet for you, check it out: Home: Buy or Rent IRR Analysis with Amortization

New for 2019!  We update our spreadsheet for tax changes related to the Tax Cuts and Jobs Act enacted last year. Try walking through our updated spreadsheet here: House Rent vs. Buy IRR