Income tax bracket calculation
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:
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:
|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|
The total tax is therefore $6,858.50. (displayed as 6,859 in the example shown).
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:
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" (G4) 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" (G4)
- 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:
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.