[原创]五种方法在Excel中计算所得税

yhn706

呵呵，好东东，收到了，谢谢

晨曦吉林

写的不错.个税的计算在年底是一件大活.

eric88

辛苦了，支持一下！！！

lalsword

好东西，顶个记号，5种方法都能看个大慨。。。

sunya_0529

原帖由 晨曦吉林 于 2008-11-19 19:08 发表
写的不错.个税的计算在年底是一件大活.

年底了，该算年终奖了吧，呵呵。贴一下最近更新的计算各种所得税的自定义函数：

=iiatax(年终奖金,4)

1. 
   
2. Function iiatax(x As Double, y As Integer) '独立所得税计算函数
   
3. 'y=1 计算中国公民工薪所得税
   
4. 'y=2 计算外国公民工薪所得税
   
5. 'y=3 计算劳务所得税
   
6. 'y=4 计算数月奖金所得税
   
7. If IsNumeric(x) = False Then
   
8. iiatax = "#NUM" '计税工资非数值
   
9. Exit Function
   
10. End If
    
11. If x < 0 Then
    
12. iiatax = "-#VALUE" '计税工资为负
    
13. Exit Function
    
14. End If
    
15. Dim basicnum As Single, i%
    
16. Dim downnum As Variant, upnum As Variant, ratenum As Variant, deductnum As Variant, laowudeduct As Variant
    
17. Dim gongxin!, laowu!, nianjiang!
    
18. downnum = Array(0, 500, 2000, 5000, 20000, 40000, 60000, 80000, 100000) '定义累进区间下限
    
19. upnum = Array(500, 2000, 5000, 20000, 40000, 60000, 80000, 100000, 100000000) '定义累进区间上限
    
20. ratenum = Array(0.05, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45) '定义累进税率
    
21. deductnum = Array(0, 25, 125, 375, 1375, 3375, 6375, 10375, 15375) '定义累进速算扣除数
    
22. Select Case y
    
23. Case 1
    
24.   basicnum = 2000 '中国公民个税起征点
    
25. Case 2
    
26.   basicnum = 4800 '外籍公民个税起征点
    
27. Case 3 '劳务所得税
    
28.   downnum = Array(0, 4000, 20000, 50000)
    
29.   upnum = Array(4000, 20000, 50000, 100000000)
    
30.   ratenum = Array(0.2, 0.2, 0.3, 0.4)
    
31.   deductnum = Array(0, 0, 2000, 7000)
    
32.   laowudeduct = Array(800, x * 0.2, x * 0.2, x * 0.2)
    
33.   For i = 0 To UBound(downnum)
    
34.    If x > downnum(i) And x <= upnum(i) Then laowu = (x - laowudeduct(i)) * ratenum(i) - deductnum(i)
    
35.   Next i
    
36. Case 4 '数月奖金计税
    
37.   For i = 0 To UBound(downnum)
    
38.    If x / 12 > downnum(i) And x / 12 <= upnum(i) Then nianjiang = x * ratenum(i) - deductnum(i)
    
39.   Next i
    
40. Case Else
    
41.   iiatax = "#N/A" '参数不能识别
    
42.   Exit Function
    
43. End Select
    
44. '计算工薪所得税
    
45. For i = 0 To UBound(downnum)
    
46. If x - (basicnum + downnum(i)) > 0 Then gongxin = gongxin + (x - (basicnum + downnum(i))) * 0.05
    
47. Next i
    
48. If y = 3 Or y = 4 Then gongxin = 0
    
49. iiatax = Format(gongxin + laowu + nianjiang, "#.00") * 1
    
50. End Function
    

复制代码

gillyang

谢谢楼主分享

lygxiaochao

我是菜鸟，要多多学习，非常感谢分享！

xqwx1138

学习了，谢谢楼主分享

fangtingyue

谢谢分享，学习了