excel个人所得税计算公式一Z8Foffice教程网
如下图所示是excel个人所得税计算公式的一个截图。Z8Foffice教程网
Z8Foffice教程网
C2单元格个人所得税计算公式为：Z8Foffice教程网
=ROUND(MAX((B2-3500)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)Z8Foffice教程网
然后下拉公式复制，可以计算出其余人员的个人所得税。Z8Foffice教程网
excel个人所得税计算公式为：（应发工资-个税起征点金额）*税率-速算扣除数Z8Foffice教程网
上面的excel个人所得税计算公式，思路讲解：Z8Foffice教程网(B2-3500)是个人所得税的应纳税所得额，{0.6,2,4,5,6,7,9}是一个数组。Z8Foffice教程网0.05*{0.6,2,4,5,6,7,9}＝{0.03,0.1,0.2,0.25,0.3,0.35,0.45} 是个人所得税的税率公式。Z8Foffice教程网5*{0,21,111,201,551,1101,2701}＝{0,105,555 ,1005,2755,5505,13505}是各级别的个人所得税扣除额。Z8Foffice教程网(B2-3500)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701} 是纳税额*各级个税税率-应扣额后的数字，如果税率对应的级别纳税额时，税率越大税额越小，因为扣减了超额的扣除数，这样保证了，缴税数组里最大的那个一定是最符合相应税率的。Z8Foffice教程网因为存在了纳税额 分界(i) ThenZ8Foffice教程网 tax = (A - b) * 税率(i) - 扣除数(i)Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网 Z8Foffice教程网ElseIf z = 2 ThenZ8Foffice教程网 If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b) / 12Z8Foffice教程网 If y = 0 And x < 0 Then tax = AZ8Foffice教程网 For i = 6 To 0 Step -1Z8Foffice教程网 If x > 分界(i) ThenZ8Foffice教程网 tax = (A - b) * (1 - 税率(i)) + 扣除数(i) + bZ8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网
ElseIf z = 3 ThenZ8Foffice教程网 If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b)Z8Foffice教程网 If y = 0 And x < 0 Then tax = AZ8Foffice教程网 For i = 6 To 0 Step -1Z8Foffice教程网 If y = 0 ThenZ8Foffice教程网 If x > 分界(i) - tax(分界(i) + b, 0, 1) ThenZ8Foffice教程网 tax = (A - b - 扣除数(i)) / (1 - 税率(i)) + bZ8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 ElseZ8Foffice教程网 If x > 12 * 分界(i) - tax(12 * 分界(i), 3500, 1) ThenZ8Foffice教程网 tax = (A - 扣除数(i)) / (1 - 税率(i))Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网 Z8Foffice教程网ElseIf z = 4 ThenZ8Foffice教程网 If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b)Z8Foffice教程网 For i = 6 To 0 Step -1Z8Foffice教程网 If y = 0 ThenZ8Foffice教程网 If x > 分界(i) - tax(分界(i) + b, 0, 1) ThenZ8Foffice教程网 tax = tax(A, 0, 1) / (1 - 税率(i))Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 ElseZ8Foffice教程网 If x > 12 * 分界(i) - tax(12 * 分界(i), 3500, 1) ThenZ8Foffice教程网 tax = tax(A, 3500, 1) / (1 - 税率(i))Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网
ElseIf z = 5 ThenZ8Foffice教程网 For i = 6 To 0 Step -1Z8Foffice教程网 If y = 0 ThenZ8Foffice教程网 If A > tax(分界(i) + b, 0, 1) ThenZ8Foffice教程网 tax = (A + 扣除数(i)) / 税率(i) + bZ8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 ElseZ8Foffice教程网 If A > tax(12 * 分界(i), b, 1) ThenZ8Foffice教程网 tax = (A + 扣除数(i)) / 税率(i)Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网 Z8Foffice教程网ElseIf z = 6 ThenZ8Foffice教程网 For i = 6 To 0 Step -1Z8Foffice教程网 If y = 0 ThenZ8Foffice教程网 If A > tax(分界(i) + b, 0, 1) ThenZ8Foffice教程网 tax = (A * (1 - 税率(i)) + 扣除数(i)) / 税率(i) + bZ8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 ElseZ8Foffice教程网 If A > tax(12 * 分界(i), b, 1) ThenZ8Foffice教程网 tax = (A * (1 - 税率(i)) + 扣除数(i)) / 税率(i)Z8Foffice教程网 Exit ForZ8Foffice教程网 End IfZ8Foffice教程网 End IfZ8Foffice教程网 NextZ8Foffice教程网 Z8Foffice教程网End IfZ8Foffice教程网tax = Round(tax + 0.0001, 2)Z8Foffice教程网End FunctionZ8Foffice教程网