How to Estimate Taxes with Spreadsheets

The tax code is complex: figuring out just how much we owe Uncle Sam is frequently accompanied with a huge headache. So just how neat would it be if we had an easy way to estimate how much federal taxes we owe?

By the end of this post, you will 1) understand how our federal tax schedule works, and 2) be on your way to creating a tax estimator spreadsheet.

What will you need to create a federal tax estimator?

• 1 spreadsheet – we’ll use Google spreadsheet!
• Tax schedules – we’ll provide these for you!
• Curiosity, creativity, and persistence

Tax Basics

Before we begin, let’s first understand a few important tax concepts. The IRS provides tax schedules, which assign different tax rates to income brackets. In other words, portions of your income fall into different ranges – or brackets – and are applied a specific tax rate.

The tax rates increase on a graduated scale, so that in general, the first dollar you make is taxed at a lower rate than the last dollar you make. You may have heard the term “marginal tax rate” before: this is the percent at which the highest portion of your income is taxed. Your average tax rate is the total taxes owed divided by your taxable income.

Keep in mind that tax brackets are defined by income ranges, are adjusted for inflation, and change depending on how you file your taxes (single or jointly).

So, let’s walk through an example here: Let’s say you are a student who held a part-time job in 2012. Your taxable annual income was $8,500. Take a look below at the federal tax schedule for a single filer.

The various tax income brackets are above. Here, in this case, your income in 2012 falls within the first tier, so taxes owed would be $850, equal to 10% of your taxable income of $8,500. Your marginal tax rate and average tax rate are both 10%.

Now what about if your income was $9,700? At this point, this income level pushes you to the 2nd bracket, which states that taxes owed equals $870 plus 15% of the amount over $8,700. Our income of $9,700 is exactly $1,000 over the break point of $8,700. So, the straight-math calculation would be $870 + 15% x $1,000, which equals $1,020.  

In this case, your marginal tax rate is 15%, because this is the percentage of tax you pay on the last dollar of your income. To calculate your average tax rate, we divide your total taxes owed ($1,020) by your taxable income ($9,700), which equals 10.5%. This is lower than your marginal rate because the lion-share of your income was applied to the first tax bracket of 10%.

And so on, and so forth. We can calculate taxes owed when given a schedule based on these two inputs:  filer type (single, jointly) and taxable income.

Tax Estimator Spreadsheet Challenge

Great! So now that we have a big picture understanding on how the tax schedule works, let’s  figure out how to set up a spreadsheet as a tax estimator.

To set it up, we have three regions: the orange region in the top upper left side is where you can insert the inputs (filer, taxable income). The green region on the left side is the tax schedules themselves, the data of which comes from the IRS. The blue region on the right is where we calculate the estimated taxes.

All these will lead to our results in the yellow region up top: the estimated federal tax in dollars, marginal tax rate, and average tax rate.

My challenge to you is this: are you able to figure out how to set up the calculations in the tax estimator spreadsheet? Use my website and video tutorials as a resource. Not sure what an IF statement is or how it works? Learn about it here. Want to learn more about data validation, or the following functions:  AND, INDEX / MATCH, or MAX? Click on the links for the tutorials. Collaborate with your friends, family, and colleagues.

There are different ways to calculate estimated taxes in both the blue and yellow regions.  In our spreadsheet, we 5 functions: IF, AND, INDEX, MATCH, and MAX. We also use the data validation function in the “user-entry” area so that anyone can toggle between a Single filer or Joint filer options. We welcome creativity.

If you have questions or need a pointer, email us (spreadsheetsolving@gmail.com) or reply to this post. We’d also love to hear how you solved this problem. To see our solution, email us and we will email you the link to our spreadsheet (we’ll refrain from posting the link here to allow everyone to take part in the challenge!)