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.
Below are direct download links to the spreadsheets covering the last few tax years:
- 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.
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.
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
To create the spreadsheet I used publicly available information in regards to federal and State of Georgia tax brackets – see links below. As a result, you will have to change the brackets and floor numbers if you file in another state. The floor numbers are not calculated but fixed amounts based on the tax brackets.
- Social Security taxable earnings and tax rates for 2020
- Tax Calculator
- State Income Tax Brackets 2020
- Federal Income Tax Brackets 2020
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.
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.