Income tax bracket calculation

In this example, the goal is to calculate the total income tax owed in a progressive tax system with multiple tax brackets, as shown in the worksheet. The article below first reviews how taxes are computed in a progressive system. Next, it shows how to accomplish this task in Excel using two different methods:

1. A modern approach that leverages the latest Excel formulas and functions
2. A traditional approach that works in older versions of Excel.

The article also explains how to make the tax rates dynamic based on the taxpayer status in cell C4. The result works like this:

Table of Contents

• About progressive tax brackets
• Tax calculation with multiple brackets
• Method 1 - A modern and elegant approach
• Marginal vs. Effective Tax Rates
• Method 2 - A traditional formula approach
• Clever MIN and MAX alternative
• Dynamic tax brackets

About progressive tax brackets

The U.S. has a progressive tax system, which means that as your income increases, so does the rate at which you're taxed. Instead of taxing all income at a single rate, income is split into different "brackets," with each portion taxed at a different rate. The more you earn, the more income falls into higher brackets, which have a progressively higher rate. The IRS publishes brackets and tax rates each year. For 2023, the tax rates for a single taxpayer look like this:

Tax calculation with multiple brackets

Taxes are calculated progressively through the brackets. For example, for a single filter with $100,000 of taxable income in 2023, the total tax owed is calculated as follows:

• In the first bracket, from $0 to $11,000, $11,000 is taxed at 10% = $1,100.
• In the second bracket, from $11,000 to $44,725, $33,725 is taxed at 12% = $4,047.
• In the third bracket, from $44,725 to $95,375, $50,650 is taxed at 22% = $11,143.
• In the fourth bracket, from $95,376 to $100,000, the remaining $4,625 is taxed at 24% = $1,110.
• The total tax is $17,400, with each bracket of income taxed at progressively higher rates.

Method 1 - A modern and elegant approach

Do you have Excel 365 or a version of Excel with the LET, DROP, and VSTACK functions? Great! Let's solve this problem in a fun, modern way. The hard part of this problem is splitting the income by bracket. These are the numbers seen in the range E7:E13. To be clear, the math is simple, but implementing the math using cell references can be tricky. In the worksheet shown, the formula used in cell E5 is:

=LET(
  income,I6,
  upper,C7:C13,
  lower,DROP(VSTACK(0,upper),-1),
  IF(income<=lower,0,
    IF(income>upper,upper-lower,income-lower))
)

This formula returns the correct income per bracket for all seven brackets at once. Compared to traditional formulas, it has several notable advantages:

1. Just one formula, so there is no need to copy and paste.
2. Only two cell references are required.
3. There is no need to lock references due to the magic of dynamic arrays.
4. Only the upper limits are required. The lower limits are generated automatically.
5. Variable names that make the formula easier to read and understand.

Let's walk through how the formula works step by step. First, to make the formula more readable and efficient by storing and reusing values, the LET function defines three variables:

=LET(
  income,I6,
  upper,C7:C13,
  lower,DROP(VSTACK(0,upper),-1),

• income: This is the income to be taxed, coming from cell I6.
• upper: The "upper" limits of the tax brackets, from range C7:C13.
• lower: This creates the "lower" limits of the tax brackets. (see below)

The lower limits are generated with the VSTACK function and the DROP function like this:

DROP(VSTACK(0,upper),-1)

The values in upper are from the range C5:C11. In array form, they look like this:

{44725;95375;182100;231250;578125;"And up"}

Inside DROP, VSTACK is used to add a zero (0) as the first value:

=VSTACK(0,upper)

The result is an array like this:

{0;11000;44725;95375;182100;231250;578125;"And up"}

This array is returned directly to the DROP function, which is configured to remove the last row:

=DROP({0;11000;44725;95375;182100;231250;578125;"And up"},-1)

The result is a new array without the "And up" value:

{0;11000;44725;95375;182100;231250;578125}

At this point, we have the upper and lower arrays correctly aligned and are ready to implement the logic.

Next, we get to the heart of the formula, which is based on a nested IF function.

IF(income<=lower,0,
  IF(income>upper,upper-lower,income-lower))

The key to understanding this code is to remember that we are processing an array of values, not just a single value. Because the upper and lower arrays each contain seven values, the formula will return seven results that will "flow" through the nested IF according to the following logic:

• If the income is less than or equal to the lower bound of a bracket, then no income falls within that bracket, so the tax contribution from this bracket is 0.
• If the income is greater than the upper limit of the current bracket, the result is the difference between the upper and lower limits (upper - lower), which represents the full taxable portion for that bracket.
• Otherwise, the result is the difference between the income and the lower limit (income - lower), which is the portion of the income that falls within that bracket.

In summary, the formula uses the upper limits in C7:C13 to compute the correct lower limits. Then, it uses the IF function and the upper and lower limits to split the income in cell I6 into the correct brackets. Once the income is split by bracket, we can easily calculate the tax per bracket with a formula like this in cell F7:

=D7:D13*E7:E13

This formula simply multiples the tax rates in D7:D13 by the income splits in E7:E13. The result is an array of 7 values like this, which spill into F7:F13:

{1100;4047;11143;1110;0;0;0}

To compute the total taxes owed in F15, we use the SUM function:

=SUM(F7:F13)

The formula in E15 serves as a sanity check to make sure that all income from I4 has been accounted for:

=SUM(E7:E13)

Single formula option

So far, we have calculated the tax per bracket and total tax with separate formulas that return "per bracket" results on the worksheet. This is a good approach because it makes it easy to validate results at any point. But what if you want to calculate the total taxes owed in a single formula? In that case, we need to add a few more lines of code to our formula. Below is the formula in cell I7, which does everything in one step:

=LET(
  income,I6,
  tax_rates, D7:D13,
  upper,C7:C13,
  lower,DROP(VSTACK(0,upper),-1),
  income_by_bracket,IF(income<=lower,0,
    IF(income>upper,upper-lower,income-lower)),
  tax_by_bracket,income_by_bracket*tax_rates,
  SUM(tax_by_bracket)
)

This new formula extends the original formula by adding three new variables:

• tax_rates - the range D7:D13
• income_by_bracket - defined by the nested IF code explained above
• tax_by_bracket - defined by income_by_bracket * tax_rates

Then, on the last line, we use the SUM function to return a result by summing the amounts in tax_by_bracket:

SUM(tax_by_bracket)

This result matches the value in cell F15. Next, let's look at how to calculate the marginal and effective tax rates.

Marginal vs. Effective Tax Rates

When discussing income tax, there are two rates you are likely to encounter: the marginal tax rate and the effective tax rate. The marginal tax is the tax rate applied to the last dollar earned. For $100,000 of income, the marginal tax rate is 24% since that’s the rate applied to income in the fourth bracket. The effective tax is the overall percentage of income paid in taxes. It’s calculated by dividing the total tax ($17,400) by the total income ($100,000), which gives an effective tax rate of 17.4%. This demonstrates that while the highest rate paid on the last portion of income is 24%, the average rate across all brackets is lower due to the progressive system. To compute the marginal tax rate in cell I6, we use the XLOOKUP function like this:

=XLOOKUP(I6,C7:C13,D7:D13,,1)

• lookup_value - from I6
• lookup_array - the upper values in C7:C13
• return_array - the tax rates in D7:D13
• if_not_found - omitted
• match_mode - 1, for an exact match or next largest

The key here is the setting for match_mode (1), which causes XLOOKUP to return the next largest tax rate when an exact match on income is not found. To compute the effective tax rate in I9, we divide the total computed tax in I7 by the taxable income in I6:

=I7/I6

The results from both formulas are formatted using Excel's percentage number format.

Method 2 - A traditional formula approach

In older versions of Excel that do not support dynamic array formulas, I think the easiest solution is to use the "From" values directly in your calculations, then use the same nested IF logic explained above to compute the income by bracket. You can see this approach in the worksheet below:

As mentioned above, the main challenge is to split the income in cell I4 by bracket in a clean and simple way. This is done with the following formula in cell E5:

=IF($I$6<=B7,0,IF($I$6>C7,C7-B7,$I$6-B7))

As the formula is copied down, the income in each bracket is calculated like this:

• If the income in I6 is less than or equal to the lower bound in B7, then no income falls within that bracket, so the tax contribution from this bracket is 0.
• If the income in I6 is greater than the upper limit (C7), the result is the difference between the upper and lower limits (C7 - B7). This is the full taxable portion for that bracket.
• Otherwise, the result is the difference between the income and the lower limit (I6 - B7), which is the remaining portion of the income that falls within that bracket.

Then, to calculate the tax in each bracket, multiply the tax rate by the income in cell F7:

=D7*E7

As this formula is copied down column F, it returns the correct income tax in each bracket.

Clever MIN and MAX alternative

If you like clever formulas, you can simplify the nested IF logic above by replacing it with a more compact alternative based on MAX and MIN like this:

=MAX(0,MIN($I$6,C7)-B7)

Both formulas apply the same logic and return the same results.

Dynamic tax brackets

At this point, we have working tax calculation formulas that split income into brackets and apply the correct tax rates. However, the tax brackets are static and apply only to "Single" taxpayers. To finish off the problem, we need to make the tax brackets dynamic based on taxpayer status, which can be one of the four categories:

• Single taxpayer
• Married filing jointly
• Married filing separately
• Head of household

To do this, we need to incorporate the master tax rate schedule below:

Note that we have four taxpayer status options in the range N15:B18, and the tax rate table in B5:J11 contains nine columns in total. The first column contains tax rates, and the next eight columns (2 columns per category) contain the "From" and "To" thresholds, which are unique to each of the four possible taxpayer status options. For convenience, tax_table (B5:J11) and status_list (B15:B18) are named ranges.

Back in the main part of the worksheet, in cell C4, we have a dropdown list based on Data Validation that refers to the status_list. The value in cell C4 drives the formulas that determine which tax rates to use. In cell B7, the formula below uses the status in cell C4 to retrieve all required data in one step:

=CHOOSECOLS(tax_table,XMATCH(C4,status_list)*2,XMATCH(C4,status_list)*2+1,1)

At a high level, we are using the CHOOSECOLS function to retrieve the columns we need in a single step. To get the "From" column, we use the XMATCH function to determine the correct numeric column index by matching the status in C4 against the status_list:

XMATCH(C4,status_list)*2

We then multiply the result by 2, since each tax status has two columns, starting with column 2. To get the "To" column, we use XMATCH again in the same way:

XMATCH(C4,status_list)*2+1

Again, we multiply the result from XMATCH by 2, but this time, we add 1 since the "To" column is the "next" column in each category. To fetch the last column, which contains the actual tax rates, we hardcode the number 1 as col_num3 inside CHOOSECOLS. After XMATCH runs, we have the following when the status is "Single taxpayer":

=CHOOSECOLS(tax_table,2,3,1)

Now, when the taxpayer status is changed, XMATCH calculates new column indexes. For example, if we choose "Married filing jointly", the "From" column becomes 4, the "To" column becomes 5, and the tax rate remains unchanged as 1.

=CHOOSECOLS(tax_table,4,5,1)

Traditional formula option

Excel 2019 does not offer the CHOOSECOLS function or the XMATCH function. A good alternative is to use INDEX and MATCH since we can use the same idea to derive column numbers. However, we do need to use three separate formulas. In Sheet2, the formula in B7 retrieves the correct "From" column like this:

=INDEX(tax_table,0,MATCH(C4,status_list,0)*2)

Note: This is a multi-cell array formula entered in the range B5:B13 with control + shift + enter.

Inside INDEX, the array is provided as tax_table, which includes nine columns, as seen above. The row_num argument is provided as zero, a special way to ask INDEX to return an entire column. The col_num argument is calculated with the MATCH function like this:

MATCH(C4,status_list,0)*2

• lookup_value - status in from C4
• lookup_array - the named range status_list (B15:B18)
• match_type - zero, for an exact match

In this configuration MATCH gets the numeric position of the status in B15:B18. We then multiply this number by 2 because each status has two columns beginning with the second column. When the filing status in C4 is set to "Single Taxpayer", MATCH returns 1 which is multiplied by 2. Inside INDEX, we have:

=INDEX(tax_table,0,2)

INDEX then returns the second column of the tax_rate table as a final result. The formula in C7 to retrieve the "To" column is almost the same, except that we add 1 to get the "next" ("To") column:

=INDEX(tax_table,0,MATCH(C4,status_list,0)*2+1)

Finally, to retrieve the tax rates, we use INDEX by itself, with col_num hardcoded as 1:

=INDEX(tax_table,0,1)

When the status in cell C4 is changed, all three formulas update, and we get the appropriate thresholds for the selected status.