# Excel实现个人所得税计算公式及实现方法详解

excel个人所得税计算公式一

C2单元格个人所得税计算公式为：

=ROUND(MAX((B2-3500)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)

excel个人所得税计算公式为：（应发工资-个税起征点金额）*税率-速算扣除数

(B2-3500)是个人所得税的应纳税所得额，{0.6,2,4,5,6,7,9}是一个数组。

0.05*{0.6,2,4,5,6,7,9}＝{0.03,0.1,0.2,0.25,0.3,0.35,0.45} 是个人所得税的税率公式。

5*{0,21,111,201,551,1101,2701}＝{0,105,555 ,1005,2755,5505,13505}是各级别的个人所得税扣除额。

(B2-3500)*5%*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701} 是纳税额*各级个税税率-应扣额后的数字，如果税率对应的级别<=纳税额时，税率越大税额越大，符合所缴税款和所得相匹配的原理。如果税率对应的级别>纳税额时，税率越大税额越小，因为扣减了超额的扣除数，这样保证了，缴税数组里最大的那个一定是最符合相应税率的。

excel个人所得税计算公式二

Function tax(Optional A As Double = 0, Optional y = 0, Optional z = 1)

'tax(月收入),tax(年收入,月收入)

Dim 分界, 税率, 扣除数

b = 3500

If z = 1 Then

If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b) / 12

For i = 6 To 0 Step -1

If x > 分界(i) Then

tax = (A - b) * 税率(i) - 扣除数(i)

Exit For

End If

Next

ElseIf z = 2 Then

If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b) / 12

If y = 0 And x < 0 Then tax = A

For i = 6 To 0 Step -1

If x > 分界(i) Then

tax = (A - b) * (1 - 税率(i)) + 扣除数(i) + b

Exit For

End If

Next

ElseIf z = 3 Then

If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b)

If y = 0 And x < 0 Then tax = A

For i = 6 To 0 Step -1

If y = 0 Then

If x > 分界(i) - tax(分界(i) + b, 0, 1) Then

tax = (A - b - 扣除数(i)) / (1 - 税率(i)) + b

Exit For

End If

Else

If x > 12 * 分界(i) - tax(12 * 分界(i), 3500, 1) Then

tax = (A - 扣除数(i)) / (1 - 税率(i))

Exit For

End If

End If

Next

ElseIf z = 4 Then

If y = 0 Then x = A - b Else b = Application.Max(b - y, 0): x = (A - b)

For i = 6 To 0 Step -1

If y = 0 Then

If x > 分界(i) - tax(分界(i) + b, 0, 1) Then

tax = tax(A, 0, 1) / (1 - 税率(i))

Exit For

End If

Else

If x > 12 * 分界(i) - tax(12 * 分界(i), 3500, 1) Then

tax = tax(A, 3500, 1) / (1 - 税率(i))

Exit For

End If

End If

Next

ElseIf z = 5 Then

For i = 6 To 0 Step -1

If y = 0 Then

If A > tax(分界(i) + b, 0, 1) Then

tax = (A + 扣除数(i)) / 税率(i) + b

Exit For

End If

Else

If A > tax(12 * 分界(i), b, 1) Then

tax = (A + 扣除数(i)) / 税率(i)

Exit For

End If

End If

Next

ElseIf z = 6 Then

For i = 6 To 0 Step -1

If y = 0 Then

If A > tax(分界(i) + b, 0, 1) Then

tax = (A * (1 - 税率(i)) + 扣除数(i)) / 税率(i) + b

Exit For

End If

Else

If A > tax(12 * 分界(i), b, 1) Then

tax = (A * (1 - 税率(i)) + 扣除数(i)) / 税率(i)

Exit For

End If

End If

Next

End If

tax = Round(tax + 0.0001, 2)

End Function