How a 6% mortgage rate should affect home prices

Today’s post will be an application of our 2019 house rent vs. buy spreadsheet. With the recent jump in mortgage rates to near 6%, we wonder how much house prices are supposed to be down (in theory) if a buyer were perfectly rational and would demand the same internal rate of return from their housing purchase decision.

We’ll pull up our mortgage spreadsheet and enter in some very average metrics for the US:

Home price: $375k, Median rent: $1,800/month, time spent in house before selling: 8 years, rent and other cost inflation: 3.5%, down payment: 20%

We’ll start our mortgage rate at 3.5%, and found the IRR to be 8.12%.

With a mortgage rate of 6%, in order to keep the IRR of buying the house at 8.12%, the house price would have to be $283,430, which is 24% less than the original house price!

A 2019 update of our House Rent vs Buy IRR Spreadsheet

This post will be a quick update to our original buy vs. rent spreadsheet post, given changes in the economics of housing due to the Tax Cuts and Jobs Act from late 2017. First, a quick review of how the original sheet worked: we had taken the main costs and benefits of home ownership as inputs and calculated what IRR you effectively were earning on your down payment:

Major changes to the spreadsheet include:

1. Mortgage interest is now only deductible for the first $750k. We adjust the formula for the tax benefit of the mortgage interest deduction to include an if statement that checks if the mortgage balance is above $750k. If so, the tax benefit is limited to deducting interest on the $750k.

2. The value of the property tax deduction is likely lower due to the cap on state, local, and property tax deductions at $10k.

3. The standard deduction is much higher: $24k for a married couple for 2019 versus $12,700 in 2017. This makes it more likely people end up just taking the standard deduction, which means the property tax deduction is not used, or could make the part of the mortgage interest that is under the $24k cap not useful.

We’ll work on both of these problems together, with some simplifying assumptions. First, we’ll ask users to input their income, average tax rate, and other federal tax deduction items (charitable giving, etc), and whether they are married. This will give a sense of whether they were already at the $10k SALT cap, and therefore implies the property tax deduction wouldn’t be worth anything to them. Also, only the portion of the mortgage interest that puts the user above the standard deduction is worth anything, so we add that logic in as well:

(36 hours later…)

Okay, we kept running into complicating factors, and the formulas we’re now using for the mortgage and property tax deductions have gotten very convoluted (check out the formula below that calculates what portion of the mortgage interest deduction is not really a marginal benefit because it was needed to put you over the standard deduction). Here’s what we have, rather than try to explain it step by step, try walking through it yourself, or if you have a better solution, let us know!

Check out the spreadsheet here: home buy vs rent complex 2019 update

How much “should” house prices be down in California?

An application of our IRR based Buy vs Rent spreadsheet

Based on a casual look at the economy, things are going pretty well –  unemployment is down and GDP growth is strong. So it would seem to make sense that U.S. home prices are up about 3.5% so far this year, right?

But if we rewind the clock by a year, many of the economic factors involved in owning a home in the U.S. have gone the wrong way:

Let’s use a spreadsheet to compare the values of three hypothetical houses in California – today versus from one year ago – based on changes in mortgage rates, marginal tax rates, and mortgage interest deductibility requirements.  Continue reading “How much “should” house prices be down in California?”

Prom Draft: NO. Prom Match: YES! (Residency Match Algorithm Spreadsheet)

Back in 2014, a group of male students had the creative but misguided idea of holding a prom draft to select dates for prom, a formal dance typically held at the end of high school. The very obvious problems involved having payments for high draft picks and taking the girls’ preferences out of the process.

With that in mind, we thought instead of a Prom draft, why not organize a Prom match? The matching system for medical residency has been in place since 1952, and even led to a Nobel Prize. In the residency match system, each applicant ranks his or her acceptable choices, and each school ranks its acceptable residents. Then the algorithm attempts to make the best or most optimal matches, among all parties.

So, let’s dive into how we can create a spreadsheet that optimizes students’ date preferences for prom. Continue reading “Prom Draft: NO. Prom Match: YES! (Residency Match Algorithm Spreadsheet)”

Make a Group Brainstorming and Idea Ranking Google Spreadsheet

Often, we find ourselves in a group meeting where we have to come up with new ideas and then choose the best one. For example…

1) A speaker is coming to talk to a group, but only has time for the best 5 questions from the group.

2) A team of journalists gives story ideas for next week’s magazine. Or a team of comedy writers throws out sketch ideas for next week’s show.

3) A group of friends comes up with ideas for their next business venture.

We’ll use #3 as our example. We use Google Spreadsheets for this project, because Google Spreadsheets easily allows multiple people to edit the same spreadsheet at the same time.  Continue reading “Make a Group Brainstorming and Idea Ranking Google Spreadsheet”

Will the Tax Cuts and Jobs Act make you pay more or less in taxes?

House and Senate Republicans have passed a major tax bill. It makes big changes to how the government taxes its citizens. Most notably, corporations and some individuals will pay less tax (corporate rate lowered to 21%, lower individual brackets, higher standard deduction), but many deductions like the state and local deduction and personal exemption will be limited to pay for it and the deficit will likely go up.

How would this affect you? Is the backlash against certain Republican lawmakers in high tax states like California fair? Do I sense a spreadsheet in the making? After 3 prior tax posts including How to Estimate Taxes, a Marriage Tax Penalty Calculator, and an analysis of the Trump Tax Plan, you would think we had suffered enough…but here we go!

Sneak Peak – Our results indicate that married Californian homeowners making under about $700k are better off under the new plan, while those making over $700k are worse off. Disclaimer – this is really complicated, we might have made some errors (please let us know if you see any), and your mileage may definitely vary:

Continue reading “Will the Tax Cuts and Jobs Act make you pay more or less in taxes?”

How to compare rewards credit cards with the NPV() function

In this post, we’ll show you how to compare different credit cards that have different sign-up bonuses, annual fees, rewards, etc. We’ll use the NPV function, which allows us to compare different streams of cash flows that come at different times.

A word of caution: Our post assumes that you’ll pay off your credit card in full each month. As we mentioned in our personal finance modeling post, the rate of return earned on investments is one of the key factors in achieving long term financial independence. We assumed getting a 4% return on our investments…the average interest rate you’d have to pay on a credit card balance is 15%! That’s like having a huge negative investment, which could keep you in the poor house. Continue reading “How to compare rewards credit cards with the NPV() function”

What’s the expected value of your Powerball ticket?

As the Powerball jackpot grows to over $300 million, we start to wonder if maybe buying a ticket is “worth it.” While the lottery is “worth it” in that ticket sales goes to things like state education, buying tickets is typically not worth it for yo because the projected payoff is far less than the ticket price. Continue reading “What’s the expected value of your Powerball ticket?”

How much money do you need to retire?

As mentioned before, we love browsing through the questions and answers on Quora. Every Quora session reveals financial success stories, inside views into jobs and companies, and some practical life advice.

One topic that comes up frequently is how much money one needs to stop working. That is a question that spreadsheets are well-suited to solve! Let’s build our Financial Freedom Spreadsheet Calculator. Continue reading “How much money do you need to retire?”

Build a weighted lottery spreadsheet to decide on lunch with friends

Taking a walk and grabbing lunch is one of the simple pleasures of the workday. Unless of course, you bring your lunch to work. Sometimes with a group of people it is hard to decide on where everyone should go to lunch together. Maybe only one person enjoys the guilty pleasure of Taco Bell, while the others want to stick with Whole Foods. Or maybe half of the group wants burgers and the other half pizza.

Here’s a relatively simple Google Mobile spreadsheet. It’s part 2 of our mobile phone spreadsheet series (See Part 1 on Tracking New Year’s Resolutions) that uses a weighted lottery to fairly determine where to go for lunch, taking into account each person’s individual preferences. Basically, each person gets 10 “points” to allocate to three restaurant choices. Each point is effectively a lottery ticket, and the spreadsheet randomly chooses the restaurant, with the probability weighted by how many points each restaurant has received. Continue reading “Build a weighted lottery spreadsheet to decide on lunch with friends”

Track New Year’s Resolutions on Mobile Sheets

This is the first in a series of posts focused on the Google Sheets app on our mobile phone, rather than the typical desktop spreadsheets. We use the mobile Google Sheets app to set our New Year’s Resolutions and track what percentage of days we have fulfilled our promises. Hopefully, having this tracker on our phone and nearby at all times makes it slightly easier to fulfill our resolutions! Continue reading “Track New Year’s Resolutions on Mobile Sheets”

Keep New Year’s Resolutions with Spreadsheets

new-year-hatWhat are your new year’s resolutions? As in most cases, coming up with the goals is easy…but achieving them is another story! According to a Forbes article published a couple of years ago, only 8% of Americans achieve their resolutions. How can we keep our resolutions? Can we be better at goal-setting? As an organizational and prioritization tool, spreadsheets can keep us accountable and help us reach our goals. In our busy lives, it can be a challenge to keep track of and prioritize everything we set out to do. Spreadsheets can help by making us better managers of our lives. Continue reading “Keep New Year’s Resolutions with Spreadsheets”