How Do I Calculate My Effective Tax Rate Using Excel?

Your effective tax rate can be calculated using Microsoft Excel through a few standard functions and an accurate breakdown of your income by tax bracket. Most of the legwork actually involves looking up the Internal Revenue Service, or IRS, tax brackets and segregating your taxable income into the correct cells.

IRS Tax Brackets

Every year, the IRS announces its tax brackets, standard deduction amounts and cost-of-living adjustments. Any announced changes are effective the following Jan. 1.

If you are preparing your 2020 tax returns in March 2021, you do not apply the announced 2021 rates. You will still apply the prior year’s (2020) rates; those should have been announced in 2019.

Segregating Earned Income

For simplicity’s sake, imagine that the tax brackets were divided into 10% increments every $25,000 of earned income. You made $80,000 during the year. The following tax rates would apply: 10% for the first $25,000, 20% for $25,001 to $50,000; 30% for $50,001 to $75,000 and 40% for $75,001 to $80,000.

Create a different cell for each income tax rate and multiply it by the amount of income you have in each bracket: $25,000 times 10% for the first, $5,000 times 40% for the last, and so on. This is your lost income per bracket.

Finding Your Effective Rate

In the above scenario, your marginal tax rate would be 40%. This is because the government will confiscate 40 cents out of every dollar that you earn above $80,000.

Your effective tax rate is different. It averages the amount of taxes you paid on all of your income. To calculate this rate, take the sum of all your lost income and divide that number by your earned income.

In the above case, the government has taken a total of $17,000 ($2,500 + $5,000 + $7,500 + $2,000). With an income of $80,000, your effective rate would be 21.25% ($17,000 / $80,000).