Using Excel Formula To Calculate Personal Income Tax
It seems that the calculation of personal income tax is rather complicated. It seems impossible to calculate only by using VBA formula instead of macro programming.
In fact, the function formula provided by Excel can not only calculate personal income tax, but also have great flexibility: it can change the tax base at will, and change the tax threshold value and tax deduction at will.
tax rate
Maybe it can be used to adjust personal income tax in the future.
)
Whether it is programming or using formulas, it is necessary to pform the method of personal income tax into mathematical formulas, and it is best to simplify this formula for future work.
Take X as your representative.
Pay taxes
Income tax (minus tax base).
salary
For example, Tax represents the income tax payable.
When 500TAX=X*10-25
When 2000TAX=X*15-125
...
By analogy, the general formula is: personal income tax = paid income * * the tax rate - deductions
Here, deduct the number = the upper limit of the taxable wage income * the scope tax rate - the last deduction.
In fact, there are only four formulas, that is, the green background.
The yellow background is the place where data are input when calculating.
The formula set up everywhere is as follows:
E3:=C3*D3-C3*D2 E2
E4-E10: fill in according to E3, or copy E3 paste to get it.
C15:=IF (B15>$B$12, B15-$B$12, 0), if the salary is greater than the non deductible base, the tax payable shall be reduced to zero or no tax base, otherwise the tax payable will be zero.
D15:=VLOOKUP (C15, $C$2:$C$10, 1) checks which tax deduction is payable for taxable wages.
E15:=C15*VLOOKUP (D15, $C$2:$E$10, 2) -VLOOKUP (D15, $C$2:$E$10, 3) refer to the deductible tax rate and the deductible deduction.
The VLOOKUP function is mainly used here, which can be consulted for more information.
The formula of C15 and D15 can be merged into E15, which is much less readable, but the form will be clearer.
The formula after merging is =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) (((0), 0, 1, 1, 3) is actually replacing the formula in the formula with its formula.
Here we simply calculate the personal income tax payment through several commonly used formulas.
In fact, there are many similar applications in Excel, so long as you use the formula well and make a reasonable combination, there will be more harvest waiting for you.
- Related reading
- Standard quality | New Standard Issued Throat To Control Pollution In Leather Processing Industry
- Handbag bank | MICHAEL KORS 2014 Early Spring Colorful Package Released
- Office attire | Learning How To Dress A Boss Is Good For Career Promotion.
- Boss interview | Zhou Haijiang: Boundless Love, Shouldering Responsibility And Carrying Forward Traditional Culture
- Material chemical industry | Where Does Adidas'S "Poison Free Future" Come From The "Addiction" Of The Fan Slap Clothing Brand?
- science and technology culture | New Fiber Fabric Can Be Used As A Sensor.
- Business School | To Achieve Pformation, Clothing Entrepreneurs Should Have International Brand Dreams.
- asset management | Small And Micro Enterprises Lack Of Financing, Regional Loan Ratio Imbalance
- Fashion shoes | Johngalliano 2014 Early Spring Holiday Shoes Series Released
- Business School | YOUNGOR'S Investment Adjustment Decided To Return To Its Main Business
- An International Multi Skilled Chinese Fashion Designer -- Yang Ziming (Cabbeen) Eleven
- Tax Saving Skills Of Consumption Tax
- Refreshing Summer To Create No Trace Of Fresh OL Makeup
- Children'S Clothing Industry In Wenzhou Is Gradually Forming The Superiority Effect Of Industrial Clusters.
- The Third Angle Of Brand Strategy
- US Cotton Is Going Up, And Short-Term PTA Will Remain The Main Shock.
- Kappa: Light Assets Operation &Nbsp; 42 Dial 1000 Jin
- Commenting Zheng Cotton Today's Trader
- Misunderstanding Of Location Selection For Garment Operators
- Fashion Bloggers Are Talented &Nbsp; Original Illustrations Match.