Spreadsheet Practice

How to calculate income tax with tax brackets in Excel

Formula (Excel and Google Sheets)

=LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2))

Look up the bracket row, then add the tax owed at its start to the income above it times its rate: =LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2)).

Result on the example
$15,154.00 in F2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

Progressive tax charges each slice of income at its own rate. Column C holds the tax already owed on all the income below each bracket. XLOOKUP with -1 returns the whole bracket row for $92,000, named band: start $48,475, rate 22%, tax at start $5,578.50. The tax is then $5,578.50 + ($92,000 − $48,475) × 22% = $15,154.

=LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2))

Result15154

  1. band{1 row × 3 cols} 48475, 0.22, 5578.5

    A name defined in LET, or a named range.

  2. F1$92,000

    The value in F1.

  3. A2:A87 rows × 1 col: $0, $11,925, $48,475, $103,350, …

    A range: 7 cells in one column.

  4. A2:C87 rows × 3 cols: $0, 10%, $0.00, $11,925, …

    A range: 7 rows × 3 columns.

  5. -1-1

    Makes the value negative.

  6. XLOOKUP(F1,A2:A8,A2:C8,,-1){1 row × 3 cols} 48475, 0.22, 5578.5

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueF1$92,000
    • lookup_arrayA2:A87 rows × 1 col: $0, $11,925, $48,475, $103,350, …
    • return_arrayA2:C87 rows × 3 cols: $0, 10%, $0.00, $11,925, …
    • if_not_found(omitted)
    • match_mode-1-1
  7. INDEX(band,1,3)5578.5

    INDEX: Value at a given row and column of a range.

    • arrayband{1 row × 3 cols} 48475, 0.22, 5578.5
    • row_num1
    • column_num3
  8. INDEX(band,1,1)48475

    INDEX: Value at a given row and column of a range.

    • arrayband{1 row × 3 cols} 48475, 0.22, 5578.5
    • row_num1
    • column_num1
  9. F1-INDEX(band,1,1)43525

    The - operator subtracts.

  10. INDEX(band,1,2)0.22

    INDEX: Value at a given row and column of a range.

    • arrayband{1 row × 3 cols} 48475, 0.22, 5578.5
    • row_num1
    • column_num2
  11. F1-INDEX(band,1,1))*INDEX(band,1,2)9575.5

    The * operator multiplies.

  12. INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2)15154

    The + operator adds.

  13. LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2))15154

    LET: Names values inside a formula.

    • name1band{1 row × 3 cols} 48475, 0.22, 5578.5
    • value1XLOOKUP(F1,A2:A8,A2:C8,,-1){1 row × 3 cols} 48475, 0.22, 5578.5
    • name2, value2INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2)15154

In Google Sheets

The same formula works in Google Sheets: =LET(band,XLOOKUP(F1,A2:A8,A2:C8,,-1),INDEX(band,1,3)+(F1-INDEX(band,1,1))*INDEX(band,1,2)). We ran it in Google Sheets mode on the same data and it returns $15,154.00.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=VLOOKUP(F1,A2:C8,3,TRUE)+(F1-VLOOKUP(F1,A2:C8,1,TRUE))*VLOOKUP(F1,A2:C8,2,TRUE)$15,154.00The same calculation with three approximate VLOOKUPs. Works in every version.
=SUMPRODUCT((F1>A2:A8)*(F1-A2:A8)*(B2:B8-VSTACK(0,B2:B7)))$15,154.00No "tax at start" column needed: each bracket adds its rate increase on the income above its start. Excel 2024 and Microsoft 365.

Common mistakes

  • Multiplying the whole income by the marginal rate, F1*22%, overstates the tax: $20,240 instead of $15,154.
  • The "tax at start" column must match the rates. Rebuild it when the brackets change: each value is the previous one plus the previous bracket's width times its rate.
  • This is taxable income, after deductions. Subtract the deduction first if you start from gross income.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate income tax with tax brackets

In F2, calculate the total tax on the taxable income in F1, taxing each slice of income at its own bracket's rate.

Questions

How do I get the effective rate?

Divide the tax by the income: =F2/F1, 16.5% here.

How do I fill in the "tax at start" column?

Start with 0 in C2, then in C3 use =C2+(A3-A2)*B2 and fill down.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.