Explanation
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:
=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.