Using Excel formula to calculate personal income tax

The calculation of personal income tax seems to be more complicated, it seems that it is impossible to use the VBA macro programming and only calculate by formula. In fact, the function formula provided by Excel can not only calculate the personal income tax, but also have a lot of flexibility: you can change the withholding tax base at will, arbitrarily change the deduction of tax subsection limit value and its deduction tax rate (may later adjust the personal income tax can be used.) )

Whether you are programming or using a formula, you have to translate the method of personal income tax into a mathematical formula, and it is best to simplify this formulation to reduce difficulties for later work. With x representing your payroll income (minus the tax-free base) (where the personal income tax is only for payroll purposes), tax represents an income tax, then:

When 500tax=x*10-25

When 2000tax=x*15-125

......

And so on, the general formula is: personal income tax = taxable Wage income * The range rate-deduction number

In this case, the deduction = The upper limit of the taxable wage income * The range rate-the deduction of the previous range

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

E3:=c3*d3-c3*d2 E2

E4-e10: According to E3 fill, or copy E3 paste get

C15:=if (b15> $B $12,b15-$B $12,0) If the income earned is greater than the tax base, then the taxable wage shall be paid minus the tax base without deduction, otherwise, the payroll tax shall be paid zero.

D15:=vlookup (C15, $C $: $C $10,1) Check which tax deductible is the taxable wage.

E15:=c15*vlookup (D15, $C $: $E $10,2)-vlookup (D15, $C $: $E $10,3) Check the deduction for tax deduction and deductible deductions. The VLOOKUP function is used here, and can be consulted to help get more information.

C15,D15 formulas can be merged into E15, which is much less readable, but the table will be clearer. Merged formula: =if (b15& gt; $B $12,b15-$B $12,0) *vlookup (VLOOKUP (IF (b15> $B $12,b15-$B $12,0), $C $ $C $10,1), $C $ $E $ 10,2)-vlookup (VLOOKUP (IF (b15> $B $12,b15-$B $12,0), $C $ $C $10,1), $C $: $E $10,3) actually replaces the c15,d15 that appears in the formula with its formula.

Here we only through a few commonly used formulas, it is easy to automatically calculate the personal income tax payment. In fact, there are a lot of similar applications in Excel, as long as we use a good formula, and a reasonable combination, then there will be more harvest waiting for you.