2019/2020 Tax Estimate Spreadsheet

Published:
Last Updated: Jan 21, 2020

Written by

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 2020.

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.

Source: Moneychimp
Source: Moneychimp

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

If you don’t see a download form below, please disable your AD blocker and reload the page. I’ve noticed some ad blockers, including the one I use, block the Javascript that loads the download form.

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.

How to estimate your taxes using Excel
How to estimate your taxes using Excel

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)

Excel Formula to Calculate Tax Projections
Excel Tax Bracket Formula

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 2018/2019 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.

The additional standard deduction for people who have reached age 65 (or who are blind) is $1,300 for each married taxpayer or $1,600 for unmarried taxpayers.

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

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.

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.

14 thoughts on “2019/2020 Tax Estimate Spreadsheet”

  1. 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?

    Reply
    • 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

      Reply
  2. 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.

    Reply
  3. 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

    Reply
  4. 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!

    Reply
  5. =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

    Reply

Leave a Comment

[Fit In 40 Seconds]
[Fit In 40 Seconds]