Tag Archives: spreadsheet

Is buying that house worth it? Part 2 of using the Internal Rate of Return (IRR)

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?


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

Adding the mortgage amortization makes the IRRs slightly worse:



Is medical school “worth it”? An introduction to Internal Rate of Return (“IRR”)

Finance hiring is down, law school grads are having a tough time finding real law jobs, so what is an ambitious but risk averse college student to do with his or her life these days? Okay, right now the answer is computer science. Yes seriously, do computer science. But let’s pretend it is the year 2001 and the only other option respectable option is medical school. But doesn’t med school take a lot of time (4 years school plus 3-7 years residency/fellowship) and cost a lot of money? How can we figure out if going to med school and not earning doctor money until 7 years from now is worth it financially relative to just entering the workforce and working for those 7 years? Continue reading

How charitable is your favorite nonprofit?

“The more you give, the more you get, that’s being alive” – The Money Song, Avenue Q

Charitable giving serves a valuable purpose  in  our society. It allows organizations in health, education, social services and others to provide benefits to people who otherwise couldn’t afford them. It allows people who have built up wealth to give back and make a difference. The federal government even subsidizes charitable giving by allowing donations to be deducted from income reported for taxes (effectively kicking in up to 39.6% of each donation). It’s a great system that is meant to fund those people and organizations in need. At least that is how it should be. Continue reading

Build a marriage tax penalty calculator spreadsheet

Summer and Fall are generally regarded as “Wedding season,” a time when love and celebration are in the air. December, on the other hand, could be regarded as “Should we get legally married for tax reasons before the end of the year” season, a slightly less romantic affair. Continue reading

Build a Spreadsheet to help you choose a healthcare plan

Will you save more with standard PPO or a high-deductible PPO Saver plan with a Health Savings Account?

It is currently Open Enrollment season at many workplaces, which is when employees choose their medical insurance and other benefits plans for the upcoming year. It’s also the time of year when people grumble “why is the US healthcare system so complicated” and just elect whatever plan they had in the prior year. Building a spreadsheet can help someone compare the costs and benefits of each of the plans under a variety of different assumptions about tax rate and healthcare expenditure. Continue reading

Pick up basketball: Should you shoot more 2 pointers or 3 pointers when you play winners’ take?

The NBA basketball season just started last week, and the three point shot is having its moment. Despite some high profile doubters, the Warriors are the reigning NBA champions due to very impressive three point shooting. Also, the total number of three pointers taken in the NBA has steadily risen over the past 35 years.

Is it better to take a three pointer or a two pointer? The theory behind this is pretty simple, and can be done without a spreadsheet. Because a three pointer is worth 50% more than a two pointer, if a team can hit three pointers with at least 2/3 the percentage that they hit two pointers, then they would be better off taking more three pointers. Continue reading

A Simple Fantasy Football Auction Draft Spreadsheet

Last year, we demonstrated how someone can make a fantasy football draft spreadsheet to help make the best decision in each round of a traditional snake draft.  While most leagues use snake draft, eventually you might be faced with a league where someone suggests an auction draft.  They might cite something like this article to make their case.  At  first you might feel intimidated, but once you remember that every auction round is just a data driven decision, and that spreadsheets are great at solving data driven decisions, you can build something to give yourself the best chance to succeed! Continue reading

Pick your son’s sport based on predicted height and weight

Subtitle 1: Get your son into Harvard through the backdoor of sports!  (The other backdoor of legacy is significantly more difficult)

Subtitle 2: Why I might not have chosen baseball if I knew I’d end up weighing 155 pounds

Continue reading

What would the Lakers’ record be if Kobe Bryant was an average shooting guard?

The Lakers are off to a rough start this year – through 9 games they are 1-8, good enough for last place in the Western Conference. In this spreadsheet problem solving example, we’ll use spreadsheets to answer a pertinent question – if the legendary Kobe Bryant played like an average shooting guard, how would the Lakers have fared this year? Continue reading

Create Study Index Cards in a Spreadsheet

Index CardsI’m studying for the SAT, ACT, or GRE exam and need to boost my vocabulary. I’m trying to remember important concepts for my chemistry test. I’d like to remember a few algebra formulas (like the Pythagorean Theorem) or geometric proofs. I want to better prepare for my foreign language quiz. Well, we’ve got something for each of you. With the school season well underway, let’s check out effective study tool tailored to your specific goals – all created with a single spreadsheet!  Continue reading

Pick your daughter’s future sport based on her expected body type (height and weight)

Women Sports

Or, “How to get your five-year old recruited into Stanford or Harvard for sports in 2027”…Or, “It helps to be tall”

Continue reading

Use a spreadsheet to check Matthew Berry’s Top 200 Fantasy Football Rankings results from Week 1

“It’s hard to make predictions, especially about the future”  Various

Usually, improvement in prediction-making comes in two steps.  Step 1: Make a Prediction.  Step 2: Evaluate how accurate the prediction was, and learn from it.  Often times, Step 2 can get overlooked as we move on to future predictions and future weeks of fantasy football.  Spreadsheets can help us quickly evaluate how our predictions were, and quickly point out where we might have erred.

Continue reading