The calculation of personal income tax looks complicated. It seems that it is impossible to calculate only with formulas without VBA macro programming. In fact, the function formula provided by Excel can not only calculate personal income tax, but also has great flexibility: you can change the non-tax deduction base at will, change the threshold value of each tax deduction segment and its tax deduction rate at will (maybe adjust the personal income tax in the future). can be used.)

Whether programming or using formulas, the method of personal income tax must be converted into a mathematical formula, and it is best to simplify this formula to reduce difficulties for future work. Let X represent your tax payable (minus the tax-free base) of your salary income (the personal income tax here is just an example of salary),

## Tax stands for income tax payable, then:

When 500TAX=X*10-25

When 2000TAX=X*15-125

……

By analogy, the general formula is: personal income tax = taxable salary income * tax rate in this range – deduction

Here, the deduction = the upper limit of the upper range of taxable salary income * the tax rate of this range – the deduction of the previous range

In fact, there are only four formulas, namely the green background. The yellow background is where the data is entered during the calculation. The formula settings are described as follows:

E3:=C3*D3-C3*D2 E2

E4-E10: Fill according to E3, or copy and paste E3

C15: =IF(B15>\\$B\\$12,B15-\\$B\\$12,0) If the salary is greater than the non-tax deductible base, the taxable salary is the salary minus zero and the non-tax deductible base, otherwise, it should be Tax pay is zero.

D15:=VLOOKUP(C15,\\$C\\$2:\\$C\\$10,1) Check which tax deduction range the taxable wages and salaries belong to.

E15: =C15*VLOOKUP(D15,\\$C\\$2:\\$E\\$10,2)-VLOOKUP(D15,\\$C\\$2:\\$E\\$10,3) Check the tax deduction rate for this tax deduction range and deductions to be reduced. The VLOOKUP function is mainly used here, you can refer to the help for more information.

The formulas of C15 and D15 can be merged into E15, so the readability will be much worse, but the table will be clearer. Combined formula: =IF(B15& gt;\\$B\\$12,B15-\\$B\\$12,0)*VLOOKUP(VLOOKUP(IF(B15>\\$B\\$12,B15-\\$B\\$12,0 ),\\$C\\$2:\\$C\\$10,1),\\$C\\$2:\\$E\\$10,2)-VLOOKUP(VLOOKUP(IF(B15>\\$B\\$12,B15-\\$ B\\$12,0),\\$C\\$2:\\$C\\$10,1),\\$C\\$2:\\$E\\$10,3) is actually the C15 and D15 appearing in the formula with their formula Can be replaced.

Here we just use a few commonly used formulas to easily and automatically calculate the payment of personal income tax. In fact, there are many similar applications in Excel. As long as you make good use of formulas and make reasonable combinations, there will be more harvests waiting for you.