How to calculate tax rate percentage in Excel

Tax rate percentage is the percentage of income that is paid as tax. It can vary depending on the income level, tax bracket, and tax system of a country. In this article, we will show you how to calculate tax rate percentage in Excel using different methods and formulas.

Method 1: Using VLOOKUP function

One way to calculate tax rate percentage in Excel is to use the VLOOKUP function. This function allows you to look up a value in a table and return a corresponding value from another column. You can use this function to find the tax rate for a given income based on a tax table.

Steps

1. Create a tax table with the income brackets and tax rates for your country. For example, the following table shows the tax rates for individuals who are residents of Australia in 2020.

2. Name the tax table range as Rates for convenience. To do this, select the tax table range, go to the Formulas tab, click on Define Name, enter Rates as the name, and click OK.
3. Enter the income value that you want to calculate the tax rate percentage for in a cell. For example, enter $50,000 in cell A1.
4. In another cell, enter the following formula to calculate the tax rate percentage:

=VLOOKUP(A1,Rates,3,TRUE)+(A1-VLOOKUP(A1,Rates,1,TRUE))*VLOOKUP(A1,Rates,2,TRUE)/A1

This formula works as follows:

• The first VLOOKUP function looks up the income value in the first column of the Rates table and returns the cumulative tax from the third column. This is the total tax up to the lower limit of the income bracket.
• The second VLOOKUP function looks up the income value in the first column of the Rates table and returns the lower limit of the income bracket from the same column. This is the income that is not taxed at the marginal rate.
• The third VLOOKUP function looks up the income value in the first column of the Rates table and returns the tax rate from the second column. This is the marginal tax rate for the income bracket.
• The formula then subtracts the lower limit of the income bracket from the income value and multiplies it by the marginal tax rate. This is the tax on the income that exceeds the lower limit of the income bracket.
• The formula then adds the cumulative tax and the tax on the excess income and divides it by the income value. This is the tax rate percentage for the income value.

5. Press Enter and you will get the tax rate percentage in the cell. For example, for an income of $50,000, the tax rate percentage is 13.72%.

Method 2: Using IF function

Another way to calculate tax rate percentage in Excel is to use the IF function. This function allows you to perform a logical test and return a value based on whether the test is true or false. You can use this function to apply different tax rates for different income ranges.

Steps

1. Create a tax table with the income brackets and tax rates for your country. You can use the same table as in Method 1.
2. Enter the income value that you want to calculate the tax rate percentage for in a cell. For example, enter $50,000 in cell A1.
3. In another cell, enter the following formula to calculate the tax rate percentage:

=IF(A1<=18000,0,IF(A1<=37000,0+(A1-18000)*0.19/A1,IF(A1<=87000,3572+(A1-37000)*0.325/A1,IF(A1<=180000,19822+(A1-87000)*0.37/A1,54232+(A1-180000)*0.45/A1))))/A1

This formula works as follows:

• The first IF function checks if the income value is less than or equal to $18,000. If yes, it returns 0 as the tax rate percentage. If no, it goes to the next IF function.
• The second IF function checks if the income value is less than or equal to $37,000. If yes, it returns the tax rate percentage as 0 plus the tax on the income that exceeds $18,000 at 19% divided by the income value. If no, it goes to the next IF function.
• The third IF function checks if the income value is less than or equal to $87,000. If yes, it returns the tax rate percentage as $3,572 plus the tax on the income that exceeds $37,000 at 32.5% divided by the income value. If no, it goes to the next IF function.
• The fourth IF function checks if the income value is less than or equal to $180,000. If yes, it returns the tax rate percentage as $19,822 plus the tax on the income that exceeds $87,000 at 37% divided by the income value. If no, it goes to the last IF function.
• The last IF function returns the tax rate percentage as $54,232 plus the tax on the income that exceeds $180,000 at 45% divided by the income value.
• The formula then divides the result by the income value to get the tax rate percentage.

4. Press Enter and you will get the tax rate percentage in the cell. For example, for an income of $50,000, the tax rate percentage is 13.72%.