Tag Archives: Microsoft Excel

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

Do you want the stock market to go up or down?

At first glance, it sounds like an obvious question – surely it is better when stocks go up, right? From watching the ads on CNBC, it would seem that higher stock prices directly translate into more steak dinners and golf vacations while lower stock prices mean bringing your own peanut butter sandwiches to work (jelly is for bull markets). Continue reading

ABCD: Analyzing Big College Data

© Photographer Daniel Gilbey|Agency: Dreamstime.com

The college decision is a big one. Are you deciding which college(s) to apply to or which one to attend? Or are you in middle or high school and interested in attending college someday? Or have you already graduated from college?

For all, let’s explore essential skills by analyzing data across 202 U.S. colleges! Continue reading

How many languages do you speak?

Over the past several months – or years for that matter – there has been a rush among American parents to teach kids Mandarin.  After-school language classes have emerged, immersion programs are gaining popularity, and videos and phone apps focused on languages have sprung up!

A recent Economist article, “Valued-Added Remodeling” (March 16, 2013) even noted that Jack Markell, Delaware’s governor, plans to expand immersion programs, such as those seen in the kindergarten classroom of Mcllvaine Early Childhood Center, where children are taught solely in Mandarin for half of each day.

Now, what’s the connection between our language skills and a spreadsheet? Continue reading

Google Spreadsheet 101

Take a spreadsheet tour with us. Here you’ll explore the layout of a typical Google spreadsheet. Along the journey, you’ll discover what menu bar is; how to add and rename worksheets; how to add, delete, and re-size rows and columns; and how to undo an action.

5 Favorite Features of Google Spreadsheets

These are among our favorite features of Google spreadsheets!  What are yours?

1.  Convenient access anywhere:  Whether you’re on your laptop, desktop, Android, iPhone, iPad, etc, you can access your Google spreadsheet.  Without worrying about saving and replacing multiple versions, you can view and edit the same spreadsheet across all devices!

Continue reading

Should schools adopt B.Y.O.T?

A new trend across schools has emerged:  B.Y.O.T for Bring Your Own Technology.   Recently, an article in the New York Times describes the debate of whether schools should adopt B.Y.O.T.   Are you a fan or a critic of B.Y.O.T?  Do the advantages of adopting this policy outweigh the drawbacks, or do they not?  How can we think about this?  Before you answer our poll, let’s first discover how we can use a spreadsheet to develop a logical framework to better inform our opinion!

Continue reading

Employment Patterns by Gender since 1948

In honor of Women’s History Month in March, check out our poll below:  what percentage of the work force consisted of women in 1948?  Keep in mind that just 28 years prior to 1948, women were first granted the right to vote.   How have employment patterns by gender changed over time?  Let’s use our spreadsheet problem solving skills to figure this out!

Continue reading