Apply nested IFs to assign tax rates based on income levels
Assigning tax rates based on income levels can be complex, but Excel's nested IF functions can make this task much simpler. This article explains how to use these functions to categorize income into different tax brackets.
Example
Here's an example table showing incomes and their respective tax rates:
| Income | Tax Rate |
|---|---|
| $25,000 | 10% |
| $45,000 | 15% |
| $85,000 | 25% |
| $150,000 | 28% |
| $200,000 | 33% |
Exercise
Input different income levels in the table and use the nested IF function to assign the correct tax rate based on the following tax brackets:
- 10% for income up to $30,000
- 15% for income between $30,001 and $50,000
- 25% for income between $50,001 and $100,000
- 28% for income between $100,001 and $150,000
- 33% for income above $150,000
Solution
When you input the incomes into the Excel sheet, the formula in the "Tax Rate" column will automatically assign the correct tax rate based on the income levels:
The nested IF function in the "Tax Rate" column evaluates the income and assigns the appropriate tax rate.
=IF(A2<=30000, "10%", IF(A2<=50000, "15%", IF(A2<=100000, "25%", IF(A2<=150000, "28%", "33%"))))This formula checks the income in cell A2 and assigns the tax rate based on the specified brackets:
- "10%" for income up to $30,000
- "15%" for income between $30,001 and $50,000
- "25%" for income between $50,001 and $100,000
- "28%" for income between $100,001 and $150,000
- "33%" for income above $150,000