Quote of the Day
A man who is empty on the inside must decorate himself on the outside.
— Bruce Lee
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.
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.
Appendix A: IRS Rate Information From 1040ES
The rate information shown in Figure 5 is from Form 1040ES, which is available here.