Forum Discussion
Formula for Tax Calculation
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 ...
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)
)