Category: Personal Finance

How much is your low interest rate mortgage worth?

One of the ideas floating around lately is that if you were lucky enough to secure a low interest rate, it is actually a hidden asset in your net worth.

Of course, it’s a little more complicated than that, for example if you move/sell you have to repay your mortgage and that asset would disappear, which is not what typical assets actually do.

But let’s have some fun for a moment and pretend that by borrowing at a low rate we have effectively shorted a 30 year bond, and see how much value we have generated by this trade.

We’ll repurpose our debt amortization spreadsheet from an earlier post.

All we need to do now is to to add a few additional inputs – how many years are left on the mortgage, and what the current mortgage rate is. We’ll then calculate the net present value of the remaining mortgage payments for each year using the current mortgage rate as the discount rate. The difference between that NPV and your mortgage principal is the “asset value”. 

In this example, in year 5 on an original $500k mortgage at 3.25%, the “asset value” of keeping this mortgage when the current market mortgage rate is 7.9% is $170k!

You can play with the Google Spreadsheet here

Understand and estimate your taxes with this spreadsheet

Tax day is coming up, and like many other families we plug a bunch of numbers into our tax software and pay/receive whatever it tells us to do. While this is effective, it doesn’t allow for much understanding of what is going on behind the scenes of how the numbers are calculated.

Also, if a family has two earners, this spreadsheet could help explain why there was a refund/underpayment by breaking out the federal and state tax rate each person paid, and compares that to what the tax rates were overall for the couple.

We repurposed the spreadsheet used for our post Will the Tax Cuts and Jobs Act make you pay more or less in taxes?, so look there for more details on how it was built. Note that this spreadsheet only works for a married couple filing in California (feel free to tweak it for your own needs. Also of course this is highly simplified with no investment income, etc).

Here’s an example, we filled in $100k income for person 1, $20k federal withholding, and $5k state withholding from their W2.  We also filled in their spouse’s $50k income and withholding, along with 12k in mortgage interest, $6k in property tax and $500 in charitable contributions, along with 2 kids”:

They would have owed roughly $18.7k in federal tax and $5,322 in state tax, for a 12.5% federal rate and 3.5% state rate.  We can see both spouses over-withheld by 7.5% and 3.5% roughly in this fictional example.

Check out the spreadsheet here: Tax Estimator

Our analysis of the $80k SALT deduction increase in the House BBB bill

We’ve had four years to mentally recover from building our analysis of the tax effects of the Tax Cuts and Jobs Act (TCJA) on a family in California. That post and the (extremely free and educational) downloadable spreadsheet highlight the power of spreadsheets and problem solving: we can figure out exactly what the tax change would be on a family in California.

Rather than guessing and hand-waving at the net effects of the bracket changes, AMT changes, and State and Local Tax (SALT) deduction changes, we showed that taxes would go up on California families earning over $700k.

So here we are again, with the House passing a version of the Build Back Better legislation, which includes an increase in the SALT deduction limit from $10k to $80k. Senator Sanders calls this a “tax break for the rich”.

We updated our spreadsheet with a third series of columns calculating federal taxes for our hypothetical California family (owns home worth 4x income, 3% mortgage, 1% property tax, 1.5% charity, etc. – you can change your assumptions as you wish) with the $80k SALT deduction. Usually on this website, we walk through step by step how the spreadsheet was built, but it is a little too involved here and easier for you to download the spreadsheet.

The first conclusion is yes, higher-income families benefit from the increase in the SALT cap. Generally, the benefit starts around $200-250k and maxes out around $1MM in income around $27k in tax savings:

But, let’s not forget that when TCJA was enacted and the SALT deduction was capped at $10k, these same higher income families in higher tax states faced a massive tax increase while taxes were cut everywhere else. For example, at $2.5MM income the tax increase was $92k, or about 3.7% of income:

Finally, if we look at the net effect of how taxes on higher income families in high tax states are doing after the House BBB SALT cap increase to $80k and TCJA together, there is still net a big tax increase on the rich, but slightly reversed by this legislation:

Download the spreadsheet here: Spreadsheet Solving BBB SALT Cap Analysis

The Bank for Kids – an educational bank ledger spreadsheet for parents

According to Scholastic, the recommended age to start giving kids an allowance is around 5-6 years. As parents of kids around that age, this makes sense: our kids are learning about coin values in school and starting to ask questions like “why can’t I buy a museum gift shop stuffed animal when you just spent four times that amount on groceries?”

With this newfound weekly cash flow, it’s a good time to introduce the concept of spending vs. saving, which is the cornerstone of many personal finance websites. When kids save, I suppose they could just keep the cash in a jar, but a more effective teaching tool would be to have them deposit it in the “Bank of Mom and Dad”, for not only safe-keeping, but also to earn an interest return on that saved money. The hope would be to illustrate the value of saving and for that savings to be “put to work” in generating even more money for them from interest.

Spreadsheet Inputs

We’ll use Google Sheets for today’s spreadsheet with the thought that we’d like our kids to view the spreadsheet (but of course not edit…).

We’ll set the spreadsheet up like one of the old “passbook accounts” that I had as a kid (and from which my subconscious is likely drawing this post from):

Passbook - Wikipedia

We’ll also have an input for the monthly interest rate. This is an interesting decision as real monthly interest rates are perhaps only 0.04% and may not actually entice your child to save any money to earn no interest. I’ll put in 3% in our example. If little Abby finds a way to borrow a million dollars at 1% and put it in our bank, we might go bankrupt (although that’ll be promising for her Wall Street career…).

Here’s how our spreadsheet looks so far:

Spreadsheet Logic and output

There’s not really much to the logic: the Date, Description, Deposit, and Withdrawal items will typically be manual inputs depending on what is happening (allowance, withdrawal to buy stuffed animal, gift from Grandma, etc.).

The balance will just be a formula (last balance + deposits – withdrawals).

For interest payments, the formula will go in the deposits. One complication is whether to pay a full month’s interest on money that was deposited during the month by just using the last balance for the interest payment. In the name of simplicity, I think that’s fine. If little Abby figures out a way to arbitrage you by putting a lot of money in on the 30th of the month and then taking it out on the 1st…her financial education would be complete.

Check out the spreadsheet here, The Family Bank. Save a copy by going to File – > Make a Copy

Build a debt amortization spreadsheet

The topic of debt can lead to some heated debate within the personal finance community. While some shun it, others see the value in borrowing for certain things, such as a house, education, a new tv (okay maybe not the tv). While this isn’t exactly a personal finance website, we can use spreadsheets to help visualize how much future money will be needed to repay the loan.

Today’s spreadsheet will walk through how to create a debt amortization spreadsheet. It will take a user’s inputs such as the amount borrowed, interest rate, term, and spit out an interest and payment table.

Spreadsheet Inputs

  • The amount borrowed, which is also known as the “principal.” This is the total loan amount.
  • The annual interest rate, which is about 3% for mortgages these days.
  • The term, which is the number of months or years over which you are borrowing.
  • Whether you want to calculate your payments monthly or annually.

Here’s what we have so far:

Spreadsheet Calculations and Output

The real magic of calculating debt payments is in the finance function PMT(). This function will take your principal, interest rate, and term, and calculate the level amortizing periodic payment. Here’s how we use it in our spreadsheet to calculate the annual payment for our example mortgage:

Next we make a schedule of each of the payment periods, where we keep a running tally of the beginning principal owed, the interest paid, the principal paid, and the ending principal owed.

Now we fill out the table. The first beginning principal comes from cell B5, the payment comes from cell B12, the interest paid is calculated by multiplying the beginning principal with the interest rate, the principal paid is the difference between the payment and interest paid, and the ending principal is the beginning principal minus the ending principal. The next period’s beginning principal is the previous period’s ending principal.

Was that clear?

That’s pretty much it. Feel free to walk through it yourself. I added in the logic to make it flexible for monthly or annual calculations.

Excel: Debt Amortization Spreadsheet

Google Sheets: Debt Amortization Spreadsheet  (you can go to File –> Save a Copy to copy it to your Google Drive to edit it)

Build a Net Worth Estimator Spreadsheet

We’ve just hit week 4 of coronavirus-related working-from-home. Hope everyone is staying safe and staying home if you are able. One thing I’m starting to miss is workplace banter – conversations about TV shows, sports, and celebrity net worths. This post was also inspired by a blog post from Root of Good detailing their net worth progression between 2004 and 2014. In any case, it seems the fascination/financial voyeurism of guessing people’s net worth is widespread, and we’ve built a spreadsheet for the job.

Spreadsheet Inputs

So, here we have another “essence of modeling” problem. We could make our spreadsheet hopelessly complicated and precise with detailed inputs for tax rates, investment allocation, debt, and spending, etc. I’m making the choice to make the spreadsheet be as simple as possible. Our 6 inputs will be:

  • The year you started working and your starting income. (Or check glassdoor.com to estimate someone else’s).
  • Your initial net worth – some people have a head start
  • Your income now – I’m going to assume that you’ve worked every year and your income has gone up (hopefully) in a straight line fashion to today.
  • The percentage of your gross salary you saved each year. The Root of Good appears to have saved around 70-80% of their income. It appears the national average is closer to 8%.
  • How aggressively you invested your savings (%stocks vs %cash). I’ve input the annual returns from stocks using this helpful data from slickcharts

We’ll go with Google Sheets for today’s spreadsheet, and just for fun, let’s try to replicate the Root of Good’s numbers:

Spreadsheet calculations and output

In our spreadsheet, we begin with our inputs for the first year. Each year we take the starting net worth, add the income net of expenses to it. We then look up what stocks returned that year and calculate the investment return from their beginning net worth. We add it all together to get the ending net worth for the year:

For each future year, we’ll increase the year by one (until we get to the current year), increase income in a straight line between their current and final income, and add the relevant investment return:

Then we can paste in the formulas from column C into the right columns:

Our spreadsheet would have predicted Root of Good’s net worth in 2014 was $1.48 million. Their post indicated it was $1.35 million. Pretty close for just the few inputs that we used!

Check out the link to our net worth estimator spreadsheet here to play with it yourself. Remember, you can save a copy to your own Google Drive by going to File –> Make a Copy.

It would be a fun exercise if you (anonymously) posted in the comments what the spreadsheet calculated for you versus your actual net worth. That data would be tailor-made for an xy-scatter plot.

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

Who was paying the Alternative Minimum Tax (AMT)? A spreadsheet

As we head into the heart of tax season, people (well, those who haven’t read Will Republican Tax Bill make you pay less taxes?) are taking stock of what the major changes are this year. One of the major differences is that the Tax Cuts and Jobs Act lowered the number of people subject to the AMT from 5.2 million to just 200,000.

But who were these 5.2 million people? Or how much income were they making that they would be subject to this tax?

The goal of our spreadsheet is to calculate taxes owed under the normal tax system, as compared to under the AMT system. Our spreadsheet calculates taxes owed under both systems for a variety of incomes and determines what income levels people owe more under the AMT system. Continue reading “Who was paying the Alternative Minimum Tax (AMT)? A spreadsheet”

Marginal tax rates don’t equal average tax rates, the spreadsheet

A very quick post today (sorry for the long hiatus; life has been pretty hectic). There has recently been a lot of buzz from Alexandria Ocasio-Cortez’ comments on instituting a 70% marginal tax rate on very high incomes.

While this site avoids commenting on rising income equality, populism, and the balance between capitalism’s incentives and growth versus its inequality, a spreadsheet is a great way to highlight the confusion between marginal and average tax rates. Continue reading “Marginal tax rates don’t equal average tax rates, the spreadsheet”

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

Build a Classroom Stock Market Game in Google Spreadsheet

There seems to be a recent push to teach personal finance concepts in high school. That makes perfect sense to us – a little less trigonometry and a little more compound interest is probably more useful in the long run. I was in 7th grade when I was first exposed to the Stock Market Game. Basically, everyone picked one stock, and we occasionally checked to see who was doing the “best” based on price appreciation.

While I’m not sure I really learned that much from that experience of watching some random number fluctuate around, maybe it did plant some subconscious seed of interest as I ended up pursuing a career in finance. I think the Stock Market Game would be better if supplemented with a lesson on exactly what a share of ownership in a corporation means (see What Makes a Good Business?). But anyways, I digress… Continue reading “Build a Classroom Stock Market Game in Google Spreadsheet”

Marriage Tax Penalty Spreadsheet Update – Republicans improve the economics of getting married!

We’ve updated our original Marriage Tax Penalty Spreadsheet using the new brackets from the Republican Tax Plan. Having done the original in a spreadsheet, all we had to do was copy the whole sheet over, and then adjust the numbers in columns A to C according to the new tax brackets, and update the data table. Easiest post ever!

Somewhat surprisingly, the new Republican tax brackets are quite beneficial for higher income married working couples, as it mostly eliminates the marriage tax penalty for those earning less than $600k combined (previously they kicked in when both people were making around $75-100k). The married brackets now are just generally double the individual brackets, except for a kink around the $500k-600k range. Continue reading “Marriage Tax Penalty Spreadsheet Update – Republicans improve the economics of getting married!”