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:

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

where "inc" (G4) 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:

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" (G4) and "rates" (B5:D11).