How does an Excel table calculate payroll tax?

How to efficiently calculate the income tax on wages? Using the functions of if, VLOOKUP and so on.

1, assume the income tax threshold for wages is 3500, the specific stage is the following table.

2, open the payroll, and fill in the relevant content, starting points, taxable income, tax rates, calculating quickly deduction, income tax should be paid.

3. Enter =if (b2-c2>0,b2-c2,0) in taxable income to indicate that if the wage is greater than the starting point (that is, the wage-start point >0), the taxable income will be calculated otherwise 0. The drop-down fills the taxable income column.

4. In the tax rate column, enter =if (d2<=1500,0.03,if (d2<=4500,1,if d2<=9000,0.2,if d2<=35000,0.25,if ( )), to determine the level of taxable income (d2<=80000,0.35,0.45). Set the number of the tax rate column as a percentage display, and drop the fill.

5, in the calculating quickly deduction number input =vlookup (e2,{0.03,0;0.1,105;0.2,555;0.25,1005;0.3,2755;0.35,5505;0.45,13505},2), indicates the lookup corresponding calculating quickly deduction number, This cell is the number of groups that find the E2 value in the {} series and returns the second number of the series. Drop-down Fill.

6, in the Taxable Income tax column input formula =D2*E2-F2, namely calculates should pay the income tax (should be paid the income tax amount x tax rate-calculating quickly deduction number). The Drop-down fill will get all the taxable income.