Withholding with an example table
Many payroll systems work out income tax withholding from a table rather than a single flat rate. Find the row the employee's wages fall in, take that row's base amount, and add a percentage of the wages above where the row starts. This page explains how a bracket table is built, works through a full week for eight employees, and shows how to look the bracket up in a spreadsheet.
The withholding tables, rates and allowance amounts on this page and in the exercises are fictional example tables for practice only. They are not current tax law, not any real agency's table, and nothing here is tax advice. Real withholding rules vary by country and state and change over time, and employers must use the official tables that apply to them.
How a bracket table works
A bracket table splits wages into bands. Each band has its own rate, and the rate applies only to the wages inside that band. Here is the fictional example table used by Copperleaf Studio for one week of pay:
| Wages over | Base withholding | Plus % of the excess |
|---|---|---|
| 0.00 | 0.00 | 0% |
| 150.00 | 0.00 | 6% |
| 500.00 | 21.00 | 12% |
| 1,200.00 | 105.00 | 18% |
| 2,500.00 | 339.00 | 24% |
| 5,000.00 | 939.00 | 30% |
Read a row like this: "If wages are over $1,200.00, withhold $105.00 plus 18% of the wages above $1,200.00." Three terms carry the whole idea:
- Bracket start ("Wages over") is where the band begins.
- Base withholding is the tax on all the wages below the bracket start, already worked out for you.
- Excess is the part of the wages above the bracket start. Only the excess is taxed at the bracket's rate.
The base amounts are not random. Each one is the tax on the full width of every band below it. The 6% band runs from $150 to $500, which is $350 wide, and 6% of $350 is $21.00: the base of the next row. Add 12% of the $700 band from $500 to $1,200, which is $84.00, and you reach $105.00. Add 18% of $1,300, which is $234.00, and you reach $339.00. Checking that the bases build up this way is a good test of any table you type in.
So the formula for every employee is the same:
Withholding = base + (wages − bracket start) × rate
The only work is finding the right row.
Why a table instead of one flat rate
A single flat rate is easy to apply, but it treats the first dollar of pay the same as the last. A bracket table lets the rate rise as wages rise, while making sure that earning one more dollar never lowers anyone's take-home pay. Because each rate applies only to its own band, crossing into a higher bracket only changes the tax on the wages above the line. That is also why the calculation looks the way it does: a fixed base for everything below the bracket, plus a percentage of what is above it. Once you see a table that way, any bracket table becomes the same three-step job.
Worked example: Copperleaf Studio
Copperleaf Studio has eight employees. Their taxable wages for the week are already worked out (after any pre-tax deductions; see pre-tax vs after-tax deductions).
Step 1: find the bracket
The right row is the last one whose "Wages over" amount is not more than the employee's wages. K. Moreau earned $2,646.00. That is over $2,500.00 but not over $5,000.00, so the row is the $2,500.00 row: base $339.00, rate 24%.
Step 2: work out the excess and the tax on it
Excess = $2,646.00 − $2,500.00 = $146.00. Tax on the excess = $146.00 × 24% = $35.04.
Step 3: add the base
Withholding = $339.00 + $35.04 = $374.04.
Here is the whole week:
| Employee | Taxable wages | Bracket start | Base | Rate | Withholding |
|---|---|---|---|---|---|
| K. Moreau | 2,646.00 | 2,500.00 | 339.00 | 24% | 374.04 |
| A. Rivera | 80.00 | 0.00 | 0.00 | 0% | 0.00 |
| F. Larsen | 5,065.00 | 5,000.00 | 939.00 | 30% | 958.50 |
| L. Adeyemi | 875.00 | 500.00 | 21.00 | 12% | 66.00 |
| C. Okafor | 1,200.00 | 1,200.00 | 105.00 | 18% | 105.00 |
| J. Silva | 2,114.00 | 1,200.00 | 105.00 | 18% | 269.52 |
| G. Mensah | 2,748.00 | 2,500.00 | 339.00 | 24% | 398.52 |
| B. Chen | 722.00 | 500.00 | 21.00 | 12% | 47.64 |
Two rows deserve a closer look. A. Rivera's $80.00 is below the first taxed band, so the top row of the table applies: base zero, rate zero, withholding zero. C. Okafor's wages are exactly $1,200.00, a bracket start. The $1,200.00 row applies, the excess is zero, and withholding is just the base, $105.00. That is the same answer the row above would give ($21.00 + 12% × $700.00 = $105.00), which is another sign the table is built correctly.
Looking up the bracket in a spreadsheet
With the table on the sheet, you don't need to read rows by eye. A lookup does it. Because most wages fall between two "Wages over" amounts, there is usually no exact match. You need an approximate-match lookup, which returns the last row whose first column is at or below the value you look up. That is exactly the definition of the bracket.
With the table in K5:M10 and the wages in B5, the three pieces are:
- Bracket start:
=VLOOKUP(B5,$K$5:$M$10,1,TRUE) - Base:
=VLOOKUP(B5,$K$5:$M$10,2,TRUE) - Rate:
=VLOOKUP(B5,$K$5:$M$10,3,TRUE)
Withholding is then the base plus (wages minus bracket start) times the rate, all in one formula if you like. Two rules make the lookup work. The table must be sorted from smallest to largest "Wages over" amount, and the range must be locked with $ signs so it doesn't move when you fill down. A first row that starts at zero means every possible wage has a row to land in.
The approximate-match idea is the same one used to look up rates and discounts in many accounting sheets. For a refresher on lookups in general, see looking up account names in Excel.
Allowances: adjusting wages before the table
The third exercise adds one more step: allowances. In the example used here, each allowance takes a fixed example allowance amount off the wages before the table is used. Maple Lane Catering uses a different fictional table and an example allowance of $100.00 each.
Adjusted wages = taxable wages − allowances × $100.00, but never below zero.
J. Silva earned $2,777.00 and claims 2 allowances. Adjusted wages = $2,777.00 − 2 × $100.00 = $2,577.00. In Maple Lane's example table, that falls in the row starting at $2,400.00, with a base of $266.70 and a rate of 21%. Withholding = $266.70 + ($2,577.00 − $2,400.00) × 21% = $266.70 + $37.17 = $303.87.
C. Okafor earned $1,245.00 with 3 allowances, so adjusted wages are $945.00. That lands in the row starting at $450.00 (base $13.20, rate 9%): $13.20 + $495.00 × 9% = $57.75.
L. Adeyemi earned only $57.00 and claims 2 allowances, worth $200.00. The subtraction would give −$143.00, but wages can't be negative. Adjusted wages are $0.00, and withholding is zero. In a spreadsheet, wrap the subtraction in MAX: =MAX(0,B5-C5*$L$12).
Common mistakes
The exercises check for each of these and tell you which one you made.
Asking the lookup for an exact match
VLOOKUP with FALSE as the last argument looks for wages that equal a "Wages over" amount. K. Moreau's $2,646.00 isn't in the table, so the lookup returns #N/A. Only C. Okafor's $1,200.00 would work, by coincidence. Use an approximate match (TRUE) to return the bracket the wages fall in.
Taking the rate from the base column
The table has three columns: bracket start, base and rate. Asking for column 2 when you want the rate returns $339.00 for K. Moreau instead of 24%. The rate on the excess is column 3.
Applying the bracket rate to all the wages
$2,646.00 × 24% = $635.04, far more than $374.04. A bracket's rate applies only to the wages above where the bracket starts. The base already covers the wages below it, at the lower rates of the earlier bands.
Leaving out the base
Working out only the tax on the excess gives K. Moreau $35.04. That ignores the $339.00 of tax on the first $2,500.00 of wages. Withholding is always base plus the tax on the excess.
Using the original wages after allowances
Looking up J. Silva's $2,777.00 instead of the adjusted $2,577.00 gives $266.70 + $377.00 × 21% = $345.87. The allowances exist to lower the wages the table applies to, so the lookup must use the adjusted wages.
Letting adjusted wages go negative
L. Adeyemi's −$143.00 is not a real wage. A negative number falls below the first row of the table, so the lookup fails. Floor the result at zero with MAX.
How to check your work
- Withholding is never more than the wages times the top rate in the table.
- An employee exactly on a bracket start gets exactly that row's base.
- Higher wages never give lower withholding. Sort the results by wages and the withholding column should rise too.
- The base of each row equals the previous row's base plus its rate times the width of its band.
Frequently asked questions
How do you calculate withholding from a bracket table?
Find the last row whose "Wages over" amount is not more than the wages. Withholding is that row's base plus its rate times the wages above the bracket start. For $2,646.00 in the example table: $339.00 + 24% × $146.00 = $374.04.
Why use an approximate-match lookup?
Wages almost never equal a bracket start exactly. An approximate match returns the last row at or below the wages, which is the bracket the wages fall in. The table must be sorted from smallest to largest.
Does moving into a higher bracket mean all wages are taxed at the higher rate?
No. The higher rate applies only to the excess above the bracket start. The base amount covers everything below it at the lower rates.
How do allowances affect withholding?
In this example, each allowance lowers the wages by a fixed example allowance amount before the table is used, and adjusted wages can't go below zero. Real methods differ, so employers must follow the official rules that apply to them.
Is this a real withholding table?
No. Every table, rate and allowance amount here is a fictional example for practice only. It is not tax law and not tax advice.