Exceljet

Quick, clean, and to the point

Income tax bracket calculation

Excel formula: Income tax bracket calculation
Explanation 

To calculate total income tax based on multiple tax brackets, you can use VLOOKUP and a rate table structured as shown in the example. The formula in G5 is:

=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)

where "inc" (G5) and "rates" (B5:D11) are named ranges, and column D is a helper column that calculates total accumulated tax at each bracket.

Background and context

The US Tax system is "progressive", which means people with higher taxable income pay a higher federal tax rate. Rates are assessed in brackets defined by an upper and lower threshold. The amount of income that falls into a given bracket is taxed at the corresponding rate for that bracket. As taxable income increases, income is taxed over more tax brackets. Many taxpayers therefore pay several different rates.

In the example shown, the tax brackets and rates are for single filers in the United States for the 2019 tax year.  The table below shows the manual calculations for a taxable income of $50,000:

Bracket Calculation Tax
10% ($9,700 - $0) x 10% $970.00
12% ($39,475 - $9,700) x 12% $3,573.00
22% ($50,000-$39,475) x 22% $2,315.50
24% NA $0.00
32% NA $0.00
35% NA $0.00
37% NA $0.00

The total tax is therefore $6,858.50. (displayed as 6,859 in the example shown).

Setup notes

1. This formula depends on VLOOKUP function in "approximate match mode". When in approximate match mode, VLOOKUP will scan through lookup values in a table (which must be sorted in ascending order) until a higher value is found. Then it will "step back" and return a value from the previous row. In the event of an exact match, VLOOKUP will return results from the matched row.

2. In order for VLOOKUP to retrieve the actual cumulative tax amounts, these have been added to the table as a helper column in column D. The formula in D6, copied down, is:

=((B6-B5)*C5)+D5

At each row, this formula applies the rate from the row above to the income in that bracket.

3. For readability, the following named ranges, are defined: "inc" (G5) and "rates" (B5:D11).

How this formula works

In G5, the first VLOOKUP is configured to retrieve the cumulative tax at the marginal rate with these inputs:

  • Lookup value is "inc" (G5)
  • Lookup table is "rates" (B5:D11)
  • Column number is 3, Cumulative tax
  • Match type is 1 = approximate match
VLOOKUP(inc,rates,3,1) // returns 4,543

With a taxable income of $50,000, VLOOKUP, in approximate match mode, matches 39,475, and returns 4,543, the total tax up to $39,475.

The second VLOOKUP calculates the remaining income to be taxed:

(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525

calculated like this:

(50,000-39,475) = 10,525

Finally, the third VLOOKUP gets the (top) marginal tax rate:

VLOOKUP(inc,rates,2,1) // returns 22%

This is multiplied by the income calculated in the previous step. The complete formula is solved like this:

=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
=4,543+(10525)*22%
=6,859 

Marginal and effective rates

Cell G6 contains the top marginal rate, calculated with VLOOKUP:

=VLOOKUP(inc,rates,2,1) // returns 22%

The effective tax rate in G7 is total tax divided by taxable income:

=G5/inc // returns 13.7%

Note: I ran into this formula on Jeff Lenning's blog over at Excel University. It's a great example of how VLOOKUP can be used in approximate match mode, and also how VLOOKUP can be used multiple times in the same formula. 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.