Using Excel to Compute Effective Tax Rate

Quote of the Day

A man who is empty on the inside must decorate himself on the outside.

- Bruce Lee


Figure 1: Income Tax Rate Versus Income When Married Filing Jointly.

Figure 1: Income Tax Rate Versus Income When Married Filing Jointly.

I have been working as a contractor for the last few months and it is now time for me to make a quarterly tax payment to the folks at the Internal Revenue Service (IRS). This involves using some tables listed on Form 1040ES (see Appendix A). The US tax code uses has a graduated tax rate; the tax rates on higher amounts of income are higher than on lower amounts. As I calculated my tax payment, I became curious as to my effective tax rate. I am going to use Excel to duplicate a graph that I saw on the Wikipedia, updated with 2018 tax rates. My version of the graph is shown in Figure 1.

I am not a tax accountant, so please do not take anything I say as tax advice – we are just looking at how to compute your effective tax rate using Excel. If you need professional help with your taxes, perhaps specifically pertaining to tax-relief-tax-scams, you may want to look at services from the likes of Federal Tax Resolution who have over thirty years in the business. If you are in need of international tax help you may want to check out some Tax Accountants Sydney services that will support what you require, but you must make sure that this fits in with what you need to do with your taxes. One complicating factor is that you must choose between four different income graduation schedules: married jointly, married separate, single, and head of household. Figure 2 shows these different schedules in one table. The IRS normally shows them as four different tables (Appendix A). I will be filing as a married person with my wife.

My Excel workbook is shown in Figure 3. You can download the workbook from here.

Figure 3: Worksheet Excerpt.

Figure 3: Worksheet Excerpt.

The calculation is fairly straightforward. Here are some calculation details (Figure 4):

  • You select your filing category and income.
  • The spreadsheet selects the correct column of income graduations (aka hurdles).
  • The tax function uses Excel's SUMPRODUCT for the calculation.
  • The function is designed to apply the lowest tax rate to every dollar earned, plus the appropriate tax increment for each income hurdle.
  • To make the calculation of the tax increments simpler, I need to include a 0 as the heading value in the tax table. I show the correct heading label by using a custom format.
Figure M: Effective Tax Rate Calculation Details.

Figure 4: Effective Tax Rate Calculation Details.

Appendix A: IRS Rate Information From 1040ES

The rate information shown in Figure 5 is from Form 1040ES, which is available here.

Figure M: IRS Rate Information from From 1040ES.

Figure 5: IRS Rate Information from Form 1040ES.

This entry was posted in Excel, Financial. Bookmark the permalink.