Tax calculation with brackets
AdvancedProgressive tax systems don't apply a single rate to all income. Instead, different portions of income are taxed at different rates. The first dollars earned are taxed at the lowest rate, and only income above each threshold is taxed at the higher rate.
In this sheet, you have five employees with their annual incomes in column B. The tax bracket table (columns E through H) defines four progressive brackets:
- Bracket 1: Income from $0 to $10,000 is taxed at 10%
- Bracket 2: Income from $10,000 to $40,000 is taxed at 20%
- Bracket 3: Income from $40,000 to $90,000 is taxed at 25%
- Bracket 4: Income above $90,000 is taxed at 32%
For example, someone earning $50,000 would pay:
- $1,000 on the first $10,000 (10%)
- $6,000 on the next $30,000 (20%)
- $2,500 on the remaining $10,000 (25%)
- Total: $9,500
Your task:
Write a formula in C2 that calculates the total tax owed for Sarah Chen's income. The formula must correctly apply each bracket rate to only the portion of income that falls within that bracket.
Fill the formula down through C6 for all employees. Use absolute references for the bracket table values so the formula copies correctly.
Need some help?
Hint 1
For each bracket, calculate the taxable amount within that range. Use MIN to cap the income at the bracket's upper limit, then subtract the bracket's lower limit. Use MAX to ensure the result is never negative.
Hint 2
The pattern for each bracket is: MAX(MIN(income, upper_limit) - lower_limit, 0) * rate. Add up all four bracket calculations.
Hint 3
Your formula structure should be: (bracket1_tax) + (bracket2_tax) + (bracket3_tax) + (bracket4_tax), where each bracket uses the MIN/MAX pattern with absolute references to the bracket table.
Related function(s)
Answer
Exercise
Tax calculation with brackets
AdvancedProgressive tax systems don't apply a single rate to all income. Instead, different portions of income are taxed at different rates. The first dollars earned are taxed at the lowest rate, and only income above each threshold is taxed at the higher rate.
In this sheet, you have five employees with their annual incomes in column B. The tax bracket table (columns E through H) defines four progressive brackets:
- Bracket 1: Income from $0 to $10,000 is taxed at 10%
- Bracket 2: Income from $10,000 to $40,000 is taxed at 20%
- Bracket 3: Income from $40,000 to $90,000 is taxed at 25%
- Bracket 4: Income above $90,000 is taxed at 32%
For example, someone earning $50,000 would pay:
- $1,000 on the first $10,000 (10%)
- $6,000 on the next $30,000 (20%)
- $2,500 on the remaining $10,000 (25%)
- Total: $9,500
Your task:
Write a formula in C2 that calculates the total tax owed for Sarah Chen's income. The formula must correctly apply each bracket rate to only the portion of income that falls within that bracket.
Fill the formula down through C6 for all employees. Use absolute references for the bracket table values so the formula copies correctly.
Need some help?
Hint 1
For each bracket, calculate the taxable amount within that range. Use MIN to cap the income at the bracket's upper limit, then subtract the bracket's lower limit. Use MAX to ensure the result is never negative.
Hint 2
The pattern for each bracket is: MAX(MIN(income, upper_limit) - lower_limit, 0) * rate. Add up all four bracket calculations.
Hint 3
Your formula structure should be: (bracket1_tax) + (bracket2_tax) + (bracket3_tax) + (bracket4_tax), where each bracket uses the MIN/MAX pattern with absolute references to the bracket table.