Formula for Tax Calculation

[Lionel-21]

I'm looking for a formula to calculate the taxes owed. I've found several examples that use an cumulative column but I can't do that as I'm going to be modeling multiple scenarios.

What I'd like is something like this (pseudo code) where column A is the estimated adjusted gross income and column B is where the formula resides - so this would be the formula in the cells in column B:

tax = rate10(a1)+rate12(a1)+rate22(a1)+rate24(a1)

rate10 would calculate the amount for the 10% bracket range, rate12 for the 12% which is the amount above the 10% range to the top of the 12% range, then fro the 22% and then the 24% range.

Thanks for any tips.

[#2]

@Lionel-21 

Here's a working model; you just need to make sure that the table is correct. I also put a limit of $10M on the income side; that can be made into 10 billion if your scenarios include that possibility.

Here's the table

The formula makes use of MATCH to find the appropriate row in the tax table,

and then CHOOSE to do the calculation based on the figures running across that row for base amount, and then percentage of excess. Income is in Cell A16 in the attached sheet.

=CHOOSE(MATCH(A16,C2:C9,1),

Income*D3,

D4+F4*(Income-H4),

D5+F5*(Income-H5),

D6+F6*(Income-H6),

D7+F7*(Income-H7),

D8+F8*(Income-H8),

D9+F9*(Income-H9)

)