Using Excel to find the marginal tax rate can be accomplished using the VLOOKUP function. However, calculating the total tax bill is a little more difficult. Below we have shown a copy of the IRS tax table for an unmarried individual for 2020. Often, tax tables are presented in this format.

In reading this table, the marginal tax rate for taxable income less than $9,875 is 10 percent. If the taxable income is between $9,875 and $40,125, the tax bill is $987.50 plus the marginal taxes. The marginal taxes are calculated as the taxable income minus $9,875 times the marginal tax rate of 12 percent.

Below, we have the tax table that was applicable for married individuals filing jointly in 2020.

a. Create a tax table in Excel for a couple filing jointly similar to the individual tax table shown above. Your spreadsheet should then calculate the marginal tax rate, the average tax rate, and the tax bill for any level of taxable income input by a user.

b. For a taxable income of $225,000, what is the marginal tax rate?

c. For a taxable income of $225,000, what is the total tax bill?

d. For a taxable income of $225,000, what is the average tax rate?

Transcribed Image Text:

If taxable income is But not over... over ... The tax is: $ 9,875 10% of the amount over $0 $987.50 plus 12% of the amount over $9,875 $4,617.50 plus 22% of the amount over $40,125 $14,605.50 plus 24% of the amount over $85,525 $33,271.50 plus 32% of the amount over $163,300 9,875 40,125 40,125 85,525 85,525 163,300 163,300 207,350 207,350 518,400 $47,367.50 plus 35% of the amount over $207,350 518,400 $156,235 plus 37% of the amount over $518,400