If you are looking to find out if you will get a tax refund or if you owe money this year, here is a simple Excel spreadsheet that can help you estimate federal and state income taxes before you file your return in 2021.
The formulas and spreadsheets shown and linked below take the new tax reform and tax cuts into account that went into effect under President Trump.
Despite being an impulsive buyer, I consider myself financially responsible. So besides keeping track of my spending, savings, and investments on a daily to weekly basis, I also run tax forecast calculations once or twice a year. I do that because I want to make sure that I’m not having withheld too little but more importantly, I don’t want to lend any money, interest-free, to the government.
Over the years I have fine-tuned my Excel spreadsheet to require as little input as necessary, especially when it comes to correctly calculate my income tax withholding, based on the various brackets.
I tried to find an example for Excel that would calculate federal and state taxes based on the 2017/2018/2019/2020 brackets. What I found was either too simple or way too complicated. So I spent some time putting together, what I consider, an accurate enough forecast. It doesn’t account for every single deduction there is, that’s what I have my CPA for, but it’s good enough to give a decent estimate.
Download Spreadsheets
Below are direct download links to the spreadsheets covering the last few tax years:
Assumptions
- Married, filing jointly
- Residing in Georgia
- IRA and 401k contributions
- Mortgage interest and property tax tax
You can easily change the assumptions, i.e., if you’re single, by changing the information highlighted in green. Use the links on the bottom to obtain the correct tax brackets for your situation.
Excel Formula to Calculate Tax
Federal Tax: =VLOOKUP(TaxableIncome,FederalTaxTable,4) + (TaxableIncome - VLOOKUP(TaxableIncome,FederalTaxTable,1)) * VLOOKUP(TaxableIncome,FederalTaxTable,3)
State Tax: =VLOOKUP(TaxableIncomeState,StatelTaxTable,4) + (TaxableIncomeState-VLOOKUP(TaxableIncomeState,StatelTaxTable,1)) * VLOOKUP(TaxableIncomeState,StatelTaxTable,3)
Thanks to the readers Kevin and Tony for simplifying the nested IF formula I had used in the past.
Supported Deductions
Below is a list of deductions the spreadsheet and formulas take into account – both on a federal and state level (where applicable):
- Itemized Deductions
- Mortgage Interest
- Property Taxes
- IRA Contributions
- 401k Pre-Tax Contributions
- Number of Children
- Number of Dependents
Output and Estimates
Based on your input (see below), the spreadsheet calculates the following:
- Adjusted Gross Income (AGI)
- Estimated Income Tax
- Taxable Income (Federal)
- Taxable Income (State)
- Federal Tax
- State Tax
- Social Security Tax
- Medicare Tax
- Child Tax Credit
- Effective Federal Tax Rate
- Effective State Tax Rate
The input required from you is (marked in yellow)
- B2-B8 for your various deductions. If B2-B4 is greater than B11, then B1 is ignored. If B2 is greater than zero, then B17 is ignored.
- F2-F6 with year-to-date information from your paycheck, including income tax withheld. So how much you earned until today.
- G2-G6 with your 2020 earning and tax estimation based on your current earnings and withholding.
It’ll then calculate your taxable income and tax projections. It’ll also tell you if you are expected to owe money or if you can expect a refund and if so, how much.
Additional Information
Please note that the “Gross Income (combined)” field takes, as the name implies, your combined income, including money earned by your spouse, social security benefits, etc. Also, you can claim an additional federal standard deduction if you are at least 65 years old or blind.
What It Does Not Account For
- AMT Exemption (AMT Patch)
- Capital Gains Taxes
- Sales Tax
- Calculate self-employment tax
- Plenty of other tax deduction options you should talk to your CPA about
Additional Information
Disclaimer: I’m not a CPA and my spreadsheet may contain errors and may be incomplete. Consult your CPA for tax advice and anything tax-related.
Alternative Solutions
If the spreadsheet above isn’t sufficient for your needs, you can also use the excellent tax calculator that comes with an Acorns account, or software like Turbo Tax.
Conclusion
It may seem intimidating to estimate your taxes before you file your return. But I hope my spreadsheet gives you a solid starting point to figure out if you will get a tax credit or if you will owe money. It’s certainly not perfect because it doesn’t cover every tax situation out there. But I found it to be reliable enough to give me a rough projection.
I’m a healthy living and technology enthusiast.
On this blog, I share in-depth product reviews, actionable information and solutions to complex problems in plain and easy-to-understand language.
*Edit
I am referencing the 2020 sheet with the IRA deduction comment.
Also, great sheet, I appreciate sharing it!
Hi Tom!
Yes, I’m aware that the spreadsheet is less than perfect but, hopefully, it’s a good enough starting point for most people.
Thanks,
Michael
In your example for 2021 I do not believe there would be a deduction for the IRA contributions.
There are income limits, which the example exceeds.
This is an excellent spreadsheet! Thank you so much for sharing!
One question — the social security max/ceiling is only for 1 person despite using MFJ tables right?
e.g.,
My understanding is that max SS withholding is 6.2% of $140,000 (+annual increases) per person.
So around $8680.
But if both married folks are working this could theoretically be as much as ~$17360 correct?
Hi Jason,
yes, I believe you are correct. I didn’t catch that because my wife isn’t working but we’re filing jointly :)
PS: Apologies for the late reply. Your comment got accidentally deleted by my anti-spam plugin and I just found out about it.
do you have a 2021 spreedsheet available?
Hi Colby,
I don’t but you should be able to update the formulas based on the new tax brackets fairly easily.
Cheers,
Michael
I believe the total state deductions formula might be wrong – if you set “Itemized deductions” to a non zero value then you lose the full standard deduction, but shouldn’t you gain back the full value of the itemized deduction? e.g. =IF(B2,0,B17)… should instead be =IF(B2,B2,B17)…
Or perhaps even =IF(B2>B17,B2,B17)…
Thanks! I believe you are correct and I updated the spreadsheets!
When would you have the year 2020 spreadsheets for download? Thanks
Hi RamS,
I just updated the article with a link to the 2020 file. See https://michaelkummer.com/wp-content/uploads/2019/04/2020-Tax-Forecast-Template.xlsx
Thank you for making this spreadsheet but I am unable to find the link to download it. I also joined your tribe but haven’t received an email as yet. Can possibly email the link to me? Much appreciated
Hi Tracy,
The download links are in the post, right under “Download Spreadsheets.”
I HAVE NO ADBLOCKER, Javascript is enabled… no download link or inline email form…
Email me the link…
Thanks
Hasn’t your mom taught you to say please, when you want something, Dave?
Mike,
This looks great but I dont see a download link or form to fill out. Am I missing something obvious?
Thanks for taking the time for what looks to be a great tool!
Ed
You’ll see the download link after entering your email address in the inline form. Maybe you have an ad blocker that prevents the form from showing.
I am not clear as to whether this calculates AMT or not?
Hey Chuck,
nope, as I mentioned in the article, my spreadsheet doesn’t consider the AMT patch.
I wasn’t clear on what you meant by “patch” And from what I saw it didn’t look like it did. But still a great tool
Thank you
Michael, Have you tackled estate tax or how to manage estate taxes via Life Insurance, Charitable Donations, etc?
Charitable donations are regular deductions afaik but I don’t have any updates as far as estate taxes are concerned. Sorry!
Might be a good idea to add a field for HSA contributions. This would impact the AGI.
How hard would it be to change state to California?
Hi Barry,
that should be easy – just get the CA brackets and update the formulas.
Cheers,
Michael
This sounds like exactly what I’ve been looking for. But, I can’t seem to find a link to download the spreadsheet. Am I missing something?
Hi Kevin,
There is a download form halfway down the article. You can enter your email address to get an email with the download links. If you submitted the form in the past, it won’t be visible anymore. If that’s the case, just use incognito mode (Chrome).
Cheers,
Michael
I believe the formula in H22 should be:
=(F21-F20)*G21+H21
And likewise fill that formula for the cells below it.
Those are marginal tax rates, but your current formula is applying to the full amount in each bracket rather than the marginal amount.
I believe this is the same problem that Ellen pointed out for the federal brackets.
Hi Tommy,
you’re right! I had it correct in the 2017 spreadsheet, but then somehow messed it up in 2018/2019. Thanks for the catch!
I don’t understand the values that are in the downloaded 2018 spreadsheet tax.
Maybe it is a difference between google sheets and excel.
There doesn’t seem to be a floor for the 12% bracket. Shouldn’t it be 1905?
This will impact the rest of the table.
Taxable Income Tax Bracket Floor
Min Max
$ – $ – 10.0%
$ 19,050.00 $ 77,399.00 12.0% $ –
$ 77,400.00 $ 164,999.00 22.0% $ 7,002.00
$ 165,000.00 $ 314,999.00 24.0% $ 26,274.00
$ 315,000.00 $ 399,999.00 32.0% $ 62,274.00
$ 400,000.00 $ 599,999.00 35.0% $ 89,474.00
$ 600,000.00 $ 100,000,000.00 37.0% $ 159,474.00
If your filing status is Married Filing Jointly (MFJ) or Qualifying Widow(er):
If your taxable income is:
Over – But not over –
$0 $19,050 10% of the amount over $0
$19,050 $77,400 $1,905 + 12% of the amount over $19,050
$77,400 $165,000 $8,907 + 22% of the amount over $77,400
$165,000 $315,000 $28,179 + 24% of the amount over $165,000
$315,000 $400,000 $64,179 + 32% of the amount over $315,000
$400,000 $600,000 $91,379 + 35% of the amount over $400,000
$600,000 ————- $161,379 + 37% of the amount over $600,000
Hi Ellen! I believe you are correct, and I fixed the error in the spreadsheet!
Does not include over 65 exemption
Hi Fred: Feel free to add them :)
Slightly more readable version of Kevin’s suggestion:
=VLOOKUP(TaxableIncome,FederalTaxTable,4)+(TaxableIncome-VLOOKUP(TaxableIncome,FederalTaxTable,1))*VLOOKUP(TaxableIncome,FederalTaxTable,3)
Just name the Taxable Income cell and the Federal Tax Table range.
Your spreadsheet saved me a ton of time! Thanks!
Thanks guys, much appreciated! I’ll update the spreadsheet in a bit.
=VLOOKUP(B11, $E$12:$H$18, 4)+VLOOKUP(B11, $E$12:$H$18, 3)*(B11-VLOOKUP(B11, $E$12:$H$18, 1))
^ this formula can be used to replace cell B12, federal tax. Simpler, and much easier to audit/no nested if statements – same idea can be applied to state tax
Love the spreadsheet, super useful