How to calculate progressive tax with brackets in one formula
✓ Verified in LibreOffice 25.8.7.3Marginal tax the way real systems work — each bracket taxes only the income inside it.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | D = bracket thresholds (0, 10000, 40000...), E = RATE INCREASES at each threshold (0.10, 0.10, 0.05 for rates 10/20/25%). |
| Google Sheets | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | Identical. |
| LibreOffice Calc | =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) | Identical. |
How it works
The trick is storing each bracket's rate INCREASE rather than its rate: 10%, then +10% (to 20%), then +5% (to 25%). Each increase applies to all income above its threshold, and SUMPRODUCT adds the pieces: 50,000×10% + 40,000×10% + 10,000×5% = $9,500 — exactly the bracket-by-bracket answer (10k@10% + 30k@20% + 10k@25%). Edit the threshold/increase table, not the formula, when rates change. Effective rate is the result ÷ income (19% here), and the marginal rate is the sum of increases passed.
Verified, not just documented
We ran =SUMPRODUCT(--(A2>D2:D4),(A2-D2:D4),E2:E4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 9500 — exactly the expected result. Every formula here is confirmed by actually executing it.