Financial
Illustrative progressive bracket calculation: base amount plus marginal rate
Learn progressive bracket arithmetic using a fictional four-bracket schedule and compare effective and marginal rates.
Calculate each slice with its own rate
The rates and thresholds here are fictional and do not represent IRS or any country tax tables. In this example the first 15000 is charged at 10%, the next 35000 at 20%, and the excess above 50000 at 30%, giving 16000 on 75000.
Approximate VLOOKUP requires ascending, distinct bracket floors. Base amounts must equal the accumulated charge from all lower brackets. The fixed A2:C5 range holds four rows; extend it explicitly for a longer schedule. Real tax calculations also depend on jurisdiction, year, deductions, credits and rounding rules.
| Bracket Floor (A) | Cumulative Base Tax (B) | Marginal Rate (C) | Tax on $75,000 Income |
|---|---|---|---|
| $0 | $0 | 10% | Full $15k slice = $1,500 |
| $15,000 | $1,500 | 20% | Full $35k slice = $7,000 (Base = $8,500) |
| $50,000 (Matched!) | $8,500 | 30% | $8,500 + ($75k - $50k)*30% = $16,000! |
| $100,000 | $23,500 | 35% | Effective Rate on $75k = 21.33% |
Apply the fictional schedule
Use nonnegative numeric income. Negative or missing input is reported rather than silently treated as zero; this example does not model loss relief.
=IF(OR(NOT(ISNUMBER(E2)),E2<0),"Invalid income",LET(inc, MAX(0, E2), VLOOKUP(inc, A2:C5, 2, TRUE) + (inc - VLOOKUP(inc, A2:C5, 1, TRUE)) * VLOOKUP(inc, A2:C5, 3, TRUE)))Calculate true Effective Tax Rate vs. Marginal Bracket Rate
Dividing total progressive tax by taxable income reveals the blended effective tax burden ($16,000 / $75,000 = 21.33%).
=IF(OR(NOT(ISNUMBER(E2)),E2<0),"Invalid income",LET(inc, MAX(0, E2), tax, VLOOKUP(inc, A2:C5, 2, TRUE) + (inc - VLOOKUP(inc, A2:C5, 1, TRUE)) * VLOOKUP(inc, A2:C5, 3, TRUE), IF(inc = 0, 0, ROUND(tax / inc, 4))))Try the example
Use the original values or change them to test your understanding.