根据工资实发数倒算应发数的自定义函数

有些福利比较好的公司会为员工承担部分工资或者奖金的个人所得税，即保证员工到手拿到多少工资，然后公司来交税。也就是通过员工的实发数来倒算应发数。

首先，我们需要一个计算个人所得税的自定义函数，代码如下：

'******计算个人所得税 '******第一个参数为应发数，第二个参数为扣除数标准，1表示中国人，其他表示外国人 Function WTax(ByVal IncTotal As Double, ByVal MyType As Integer) Dim dblKZS As Double '扣除数 '判断是中国人还是外国人来计算扣除数 dblKZS = IIf(MyType = 1, 2000, 4800) If (IncTotal - dblKZS) < 0 Then WTax = 0 Else Select Case (IncTotal - dblKZS) Case Is <= 500 WTax = (IncTotal - dblKZS) * 0.05 Case Is <= 2000 WTax = (IncTotal - dblKZS) * 0.1 - 25 Case Is <= 5000 WTax = (IncTotal - dblKZS) * 0.15 - 125 Case Is <= 20000 WTax = (IncTotal - dblKZS) * 0.2 - 375 Case Is <= 40000 WTax = (IncTotal - dblKZS) * 0.25 - 1375 Case Is <= 60000 WTax = (IncTotal - dblKZS) * 0.3 - 3375 Case Is <= 80000 WTax = (IncTotal - dblKZS) * 0.35 - 6375 Case Is <= 100000 WTax = (IncTotal - dblKZS) * 0.4 - 10375 Case Is > 100000 WTax = (IncTotal - dblKZS) * 0.45 - 15375 End Select End If WTax = WorksheetFunction.Round(WTax, 2) End Function

根据实发数倒算应发数的自定义函数如下：

'******通过实发数倒算应发数 '******第一个参数为实发数 '******第二个参数表示当月之前是否发过工资，若发过则填写应发数，若没有发过则填写0 '******第三个参数为扣除数标准，1表示中国人，其他表示外国人 Function Inc2IncTax(dblSF As Double, dblQYF As Double, MyType As Integer) Dim dblKCS As Double '扣除数 Dim dblQSF As Double '前实发 Dim dblZSF As Double '总实发 Dim dblSSS As Double '速算扣除数 Dim dblZSL As Double '税率 dblKCS = IIf(MyType = 1, 2000, 4800) '扣除数 '计算前面的实发 dblQSF = dblQYF - WTax(dblQYF, MyType) '计算总的实发 dblZSF = dblQSF + dblSF '根据总实发计算税率和扣除数 Select Case dblZSF - dblKCS Case Is < 0 dblSSS = 0 dblZSL = 0 Case 0 To 475 dblSSS = 0 dblZSL = 0.05 Case 475 To 1825 dblSSS = 25 dblZSL = 0.1 Case 1825 To 4375 dblSSS = 125 dblZSL = 0.15 Case 4375 To 16375 dblSSS = 375 dblZSL = 0.2 Case 16375 To 31375 dblSSS = 1375 dblZSL = 0.25 Case 31375 To 45375 dblSSS = 3375 dblZSL = 0.3 Case 45375 To 58375 dblSSS = 6375 dblZSL = 0.35 Case 58375 To 70375 dblSSS = 10375 dblZSL = 0.4 Case Is > 70375 dblSSS = 15375 dblZSL = 0.45 End Select Inc2IncTax = WorksheetFunction.Round((dblZSF - dblSSS - dblKCS * dblZSL) / (1 - dblZSL) - dblQYF, 2) End Function

使用方法如下：
1、比如中国员工当月实发工资2500元，只要输入以下公式：

=inc2inctax(2500,0,1)

2、如果是外国员工，当月之前已经应发工资50000元，本月还要实发5000元奖金，计算公式如下。计算得出的是这笔5000元奖金的应发数，其与实发数之间的差额即为扣税：

=inc2inctax(5000,50000,2)