Financial
Income tax bracket formula: calculate progressive marginal and effective tax
Compute progressive multi-bracket tax in a single SUMPRODUCT formula using differential bracket rates without nested IF ladders.
How differential rates eliminate nested IF tax formulas
This hypothetical three-bracket schedule uses 10% above 0, 12% above 11,000, and 22% above 44,725. It is not a current or complete tax table for any jurisdiction.
Storing the incremental rate jump in Column C (10%, +2%, +10%) lets SUMPRODUCT(--(Income > Floor), Income - Floor, DiffRate) tax every dollar at 10%, add 2% on dollars above 11,000, and add 10% on dollars above 44,725.
| Floor (A) | MarginalRate (B) | DiffRate (C) | Tax on 60,000 Income |
|---|---|---|---|
| 0 | 0.1 | 0.1 | 60,000 * 10% = 6,000.00 |
| 11000 | 0.12 | 0.02 | 49,000 * 2% = 980.00 |
| 44725 | 0.22 | 0.1 | 15,275 * 10% = 1,527.50 |
Total progressive tax and effective tax rate
At 60,000 taxable income, total tax is 8,507.50—an effective tax rate of 14.18%, even though the top marginal bracket is 22%.
=ROUND(SUMPRODUCT(--(D2 > A2:A4), D2 - A2:A4, C2:C4), 2)Effective rate vs top marginal bracket
Divide total tax by taxable income for the effective rate, and use INDEX/MATCH(..., 1) to look up the top marginal rate.
=IF(D2<=0, "Undefined", ROUND(SUMPRODUCT(--(D2 > A2:A4), D2 - A2:A4, C2:C4) / D2, 4))Inputs and interpretation
Use nonnegative taxable income and all relevant bracket floors. Sort floors ascending for approximate MATCH, and derive each differential rate as the current marginal rate minus the preceding one. The first differential rate equals the first rate. Deductions, credits, filing status, other taxes and phase-outs are not modelled. At an exact floor, the lookup returns the rate for additional income in the new bracket.
Try the example
Use the original values or change them to test your understanding.