My (Simplistic) Yearly Income Tax Planning Spreadsheet

Occasionally when I talk to some folks they are frustrated with the taxes that are due (or the refund they will be getting) when they file their taxes (more often frustrated with federal than state, and definitely more frustrated when they owe). Sometimes they owe a huge amount and don’t know how it happened. Other times they are getting a huge refund and are very happy, but really shouldn’t be because they had too much withheld and lost the use of that money for the better part of a year. So I thought I would briefly share how we manage this in our household.

At the most basic level, there are really two pieces you need to be aware of: what your tax liability is going to be and how much you are having withheld. Let’s look at each of those separately.

Your Tax Liability

Your tax liability is the total amount of taxes you owe, at both the federal and state level (if you have a state income tax). One important distinction is that often when many people refer to the taxes they “owe”, they talk about what they have to pay when they file their taxes (if they didn’t withhold enough). Or, if they are getting a refund, they’ll say, “I didn’t owe any taxes!” While this might be semantics for some folks, I think it’s important to realize that this is not an accurate definition. Almost everyone has a tax liability (particularly if you are reading this post), and almost everyone will pay taxes each year. For your 2022 or 2023 federal return (form 1040), look at line 24. That’s your tax liability. Your tax liability is not the same thing as what you pay when you file your taxes (or get back as a refund).

Your Withholding (and other tax payments)

From your tax liability, you then subtract off any payments of taxes you have already made, which is typically withholding from your paycheck/pension/social security check, but can also include prepayments you may have made as well as some tax credits like the earned income tax credit or the American Opportunity Tax Credit. Line 33 shows your total payments.

What You Pay (or your refund)

The difference is then either what you pay to the IRS when you file your taxes (if your tax liability exceed your payments, line 37) or your refund (if your payments exceed your tax liability, line 34).

How to Better Manage This

For folks who have fairly regular income, it’s often pretty easy to manage, as the withholding from their paychecks is often reasonably close to their tax liability. But for people whose income and withholding varies, or have multiple jobs, or have additional income on the side (self-employment, investment income, etc.), it can be a bit trickier. This is one of the reasons why wealthier people tend to hire CPAs to do their taxes, as they will also help them manage their withholding (and prepayments) so there are no surprises when they file their taxes.

But for many of us a CPA isn’t really necessary and we’d rather avoid the hassle and expense (after all, filing your taxes via software can be free and easy). So what we’ve done is create a spreadsheet (of course). This spreadsheet used to be much, much simpler, but as time has gone by (and as I have more time on my hands) I’ve “enhanced” it a bit. Before I link to the spreadsheet, some very important caveats.

• I am not a CPA or tax attorney; use at your own risk :-).
• This spreadsheet is not perfect. It does not handle all variables and is not intended to get the exact correct number. (My goal for our own purposes is to be within ±15% of our total tax liability).
• Even for the variables it’s intended to handle, it is not complete and could even be incorrect. (But since it’s worked well for us, I’m sharing it.)
• There’s a reason you use tax software (or a CPA) to file your taxes, because the tax code is complicated. This is not meant to replace either of those.

So, here’s the spreadsheet (filled in with some sample numbers, but with the correct tax bracket info for 2024). The first tab is for people filing single (like our daughter), and the second for people filing married filing jointly (like my wife and me). (Obviously there are other categories, but you can easily modify the spreadsheets if you need to.) Each year I copy the spreadsheet over, change the appropriate numbers (tax brackets, standard deduction, IRA/401k/403b/457b limits, etc.) and then it’s pretty much good to go. I simply then modify our income and withholding with each paycheck (pension check for us).

Occasionally there is a big enough change in the tax code that I have to make other changes to the spreadsheet.

To get the basic lay of the land of the spreadsheet, the first columns are for you to enter your taxable income and withholding from each paycheck. (We get paid monthly so only need 12, but I’ve made space for 26 to handle monthly, twice a month, and every two weeks. If you get paid weekly, you could either add some rows or simply wait and combine two weeks together.) Note that I chose to input taxable income, not gross income. I used to do gross income, and then have all the stuff subtracted off, but I realized it was easier for me just to input the taxable from the get go. (You obviously could modify this if you want.) There are also columns to add other ordinary income (think interest on your bank account or non-qualified dividends in your taxable investment account). Then as you move to the right there are some places for self-employment income and long-term capital gains/qualified dividends. It then does some calculations based on the different tax brackets and spits out an estimate of what we are going to owe (or get back as a refund). It also estimates Colorado’s fairly straightforward state taxes for us as well.

Note that when I copy it over each year, I update the tax bracket and similar info from the tax code that changes, but I leave the income and withholding from the previous year as a place to start (because you need full-year totals to see how close you are). I then simply modify as each paycheck/pension check comes in, and then copy that down for the rest of the checks if I expect them to be similar.

Again, it’s important to remember that this will not be perfect. My goal is to get within ±15% of our tax liability, as that’s close enough for our needs.

Note that there are cells to put our actual federal and state taxes due when we complete our taxes. Those are there so that I can see how close the spreadsheet was and to tell me if I need to dive in and modify something for the future.

There are many variables this spreadsheet doesn’t include and, even the ones it does, it does in a simplistic fashion.

For example, for self-employment income it just calculates it based on total self-employment income instead of accounting for any business deductions you might have.

But, as flawed as this is, it allows me to track in real-time how close our withholding is tracking our eventual tax liability, which then allows me to adjust the withholding as necessary. How do we adjust? When we see we need to change something, we go in (for us, to our pension logins since we are retired) and increase or decrease the withholding. (For folks who are still employed, you should be able to easily do this within your payroll system.) Keep in mind you can make those adjustments at any time and they should take effect with your next paycheck (assuming you’ve met whatever deadline your payroll system has).

So if you are frustrated with your 2023 tax return result and want a way to make a better prediction for 2024, perhaps give this spreadsheet a try. (You could also “check it” by changing it back to 2023 tax code numbers by changing the tax brackets and then enter in your 2023 info to see how close it comes.) And, if as 2024 progresses, you appear to be missing the mark, change those withholdings. Managing this process can be reasonably easy for most (but not all) people with just a little time and effort along the way.

Edit 3/8/24: I may not have been clear enough in my initial post that this spreadsheet is forward looking. It’s designed to be used to estimate your future tax liability and withholding amounts. For example, we are currently in 2024, so each month I updated the spreadsheets with that month’s paychecks (pension checks for us), other ordinary income (interest, dividends, etc.) and any self-employment income. For the months of 2024 that haven’t occurred yet, I project those numbers forward. That way I always have a running total of my expected tax liability and expected tax withholding for the year and can make adjustments if they are not close to each other. Not sure if this note helps, but I figured I’d give it a shot.