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

Leave a Reply

Your email address will not be published. Required fields are marked *