个税精灵EXCEL WPS VBA源码 版本1.0 2017-12-31

个税精灵EXCEL WPS VBA源码 版本1.0 更新日期2017-12-31,最新个人所得税计算方法函数,Excel、WPS自定义函数,免费下载使用,代码程序不断更新中,为了确保数据的准确性,请从个税精灵官网下载。

Option Explicit

Type TaxRank

rate As Double

deduct As Double

index As Integer

End Type

Type GongZiInfo

income As Double

insure As Double

baseLine As Double

taxIncome As Double

rate As Double

deduct As Double

tax As Double

incomeAT As Double

End Type

Type YearAwardInfo

income As Double

average As Double

rate As Double

deduct As Double

tax As Double

incomeAT As Double

End Type

Type LabourInfo

income As Double

fee As Double

taxIncome As Double

rate As Double

deduct As Double

tax As Double

incomeAT As Double

End Type

'工资薪金所得个人所得税计算方法汇总

Function GTAX(income As Double, insure As Double, baseLine As Double) '获取工资应缴纳的个人所得税

GTAX = G_CALC(income, insure, baseLine).tax

End Function

Function GRATE(income As Double, insure As Double, baseLine As Double) '获取工资对应的个税税率

GRATE = G_CALC(income, insure, baseLine).rate

End Function

Function GINCOMEAT(income As Double, insure As Double, baseLine As Double) '获取税后工资收入金额

GINCOMEAT = G_CALC(income, insure, baseLine).incomeAT

End Function

Function GDEDUCT(income As Double, insure As Double, baseLine As Double) '获取工资对应的速算扣除数

GDEDUCT = G_CALC(income, insure, baseLine).deduct

End Function

Function GTAXINCOME(income As Double, insure As Double, baseLine As Double) '获取工资对应的应纳税所得额

GTAXINCOME = G_CALC(income, insure, baseLine).taxIncome

End Function

Function GINCOMEBYAT(incomeAT As Double, insure As Double, baseLine As Double) '根据税后收入反算税前收入

GINCOMEBYAT = G_GetIncomeByIncomeAT(incomeAT, insure, baseLine)

End Function

Function GINCOMEBYT(tax As Double, insure As Double, baseLine As Double) '根据个税金额反算税前收入

GINCOMEBYT = G_GetIncomeByTax(tax, insure, baseLine)

End Function

'年终奖个人所得税计算方法汇总

Function NTAXINCOME(yearAward As Double, gongZi As Double, insure As Double, baseLine As Double) '获取年终奖应纳税所得额

NTAXINCOME = N_GetTaxIncome(yearAward, gongZi, insure, baseLine)

End Function

Function NINCOMEBYT(tax As Double) '根据年终奖个税反算税前年终奖

NINCOMEBYT = N_GetYearAwardByTax(tax)

End Function

Function NINCOMEBYAT(incomeAT As Double) '根据税后年终奖反算税前年终奖(较小值)

NINCOMEBYAT = N_GetMinYearAwardByIncomeAT(incomeAT)

End Function

Function NINCOMEBYAT2(incomeAT As Double) '根据税后年终奖反算税前年终奖(较大值)

NINCOMEBYAT2 = N_GetMaxYearAwardByIncomeAT(incomeAT)

End Function

Function NINCOMEG(incomeAT As Double, gongZi As Double, insure As Double, baseLine As Double) '根据税后年终奖反算税前年终奖(较小值)(考虑工资是否缴税)

NINCOMEG = N_GetIncomeByIncomeATWithGongZi(True, incomeAT, gongZi, insure, baseLine)

End Function

Function NINCOMEG2(incomeAT As Double, gongZi As Double, insure As Double, baseLine As Double) '根据税后年终奖反算税前年终奖(较大值)(考虑工资是否缴税)

NINCOMEG2 = N_GetIncomeByIncomeATWithGongZi(False, incomeAT, gongZi, insure, baseLine)

End Function

Function NGDIFF(gongZi As Double, insure As Double, baseLine As Double) '工资年终奖差额

NGDIFF = N_DIFF(gongZi, insure, baseLine)

End Function

Function NBINCOME(yearAward As Double, gongZi As Double, insure As Double, baseLine As Double) '最佳年终奖

NBINCOME = N_GetBestYearAward(yearAward, gongZi, insure, baseLine) '此时的最佳工资应为 = 原年终奖 - 最佳年终奖 + 原工资

End Function

Function NTAX(income As Double) '获取年终奖应纳税额

NTAX = N_CALC(income).tax

End Function

Function NTAXG(yearAward As Double, gongZi As Double, insure As Double, baseLine As Double) '获取年终奖应纳税额(考虑工资是否缴税)

NTAXG = NTAX(NTAXINCOME(yearAward, gongZi, insure, baseLine))

End Function

Function NRATE(income As Double) '获取年终奖对应的适用税率

NRATE = N_CALC(income).rate

End Function

Function NDEDUCT(income As Double) '获取年终奖对应的速算扣除数

NDEDUCT = N_CALC(income).deduct

End Function

Function NAVERAGE(income As Double) '获取年终奖平均每月工资

NAVERAGE = N_CALC(income).average

End Function

Function NBLIND(income As Double) '获取年终奖是否在“盲区”中

NBLIND = N_BLIND(income)

End Function

Function NINCOMEAT(income As Double) '获取年终奖税后金额

NINCOMEAT = N_CALC(income).incomeAT

End Function

'年薪个人所得税计算方法汇总

Function NXBNINCOME(yearIncome As Double) '获取最优年终奖

NXBNINCOME = NX_GetBestYearAward(yearIncome)

End Function

'劳务报酬所得个人所得税计算方法汇总

Function LTAX(income As Double) '获取劳报酬应缴纳的个人所得税

LTAX = L_CALC(income).tax

End Function

Function LFEE(income As Double) '获取劳务报酬的费用扣除金额

LFEE = L_CALC(income).fee

End Function

Function LTAXINCOME(income As Double) '获取报酬的应纳税所得额

LTAXINCOME = L_CALC(income).taxIncome

End Function

Function LRATE(income As Double) '获取劳务报酬对应的个税税率

LRATE = L_CALC(income).rate

End Function

Function LDEDUCT(income As Double) '获取劳务报酬对应的速算扣除数

LDEDUCT = L_CALC(income).deduct

End Function

Function LINCOMEAT(income As Double) '获取劳务报酬的税后收入

LINCOMEAT = L_CALC(income).incomeAT

End Function

Function LINCOMEBYAT(incomeAT As Double) '根据劳务报酬税后收入反算税前收入

LINCOMEBYAT = L_GetIncomeByIncomeAT(incomeAT)

End Function

Function LINCOMEBYT(tax As Double) '根据劳务报酬缴纳个人所得税反算税前收入

LINCOMEBYT = L_GetIncomeByTax(tax)

End Function

'工资

Private Function G_CALC(income As Double, insure As Double, baseLine As Double) As GongZiInfo

Dim taxIncome As Double

Dim tax As Double

Dim info As GongZiInfo

Dim rank As TaxRank

info.income = income

info.insure = insure

info.baseLine = baseLine

taxIncome = info.income - info.insure - info.baseLine

If taxIncome > 0 Then

info.taxIncome = taxIncome

rank = G_GetTaxRank(info.taxIncome)

info.rate = rank.rate

info.deduct = rank.deduct

tax = info.taxIncome * info.rate - info.deduct

tax = Round(tax, 2)

info.tax = tax

info.incomeAT = info.income - info.insure - info.tax

Else

info.taxIncome = 0

info.rate = 0

info.deduct = 0

info.tax = 0

info.incomeAT = info.income - info.insure

End If

G_CALC = info

End Function

Private Function G_GetIncomeByIncomeAT(incomeAT As Double, insure As Double, baseLine As Double) As Double

Dim income As Double

Dim taxIncome As Double

Dim rank As TaxRank

If (incomeAT - baseLine) > 0 Then

rank = G_GetTaxRankByIncomeAT(incomeAT, baseLine)

taxIncome = (incomeAT - baseLine - rank.deduct) / (1 - rank.rate)

taxIncome = Round(taxIncome, 2)

income = taxIncome + insure + baseLine

Else

income = incomeAT + insure

End If

G_GetIncomeByIncomeAT = income

End Function

Private Function G_GetIncomeByTax(tax As Double, insure As Double, baseLine As Double) As Double

Dim income As Double

Dim rank As TaxRank

Dim taxIncome As Double

income = 0

rank = G_GetTaxRankByTax(tax)

taxIncome = (tax + rank.deduct) / rank.rate

taxIncome = Round(taxIncome, 2)

income = taxIncome + insure + baseLine

G_GetIncomeByTax = income

End Function

Private Function G_GetTaxRank(taxIncome As Double) As TaxRank

Dim rank As TaxRank

If taxIncome <= 1500 Then

rank.index = 1

rank.rate = 0.03

rank.deduct = 0

ElseIf taxIncome > 1500 And taxIncome <= 4500 Then

rank.index = 2

rank.rate = 0.1

rank.deduct = 105

ElseIf taxIncome > 4500 And taxIncome <= 9000 Then

rank.index = 3

rank.rate = 0.2

rank.deduct = 555

ElseIf taxIncome > 9000 And taxIncome <= 35000 Then

rank.index = 4

rank.rate = 0.25

rank.deduct = 1005

ElseIf taxIncome > 3500 And taxIncome <= 55000 Then

rank.index = 5

rank.rate = 0.3

rank.deduct = 2755

ElseIf taxIncome > 55000 And taxIncome <= 80000 Then

rank.index = 6

rank.rate = 0.35

rank.deduct = 5505

Else

rank.index = 7

rank.rate = 0.45

rank.deduct = 13505

End If

G_GetTaxRank = rank

End Function

Private Function G_GetTaxRankByIncomeAT(incomeAT As Double, baseLine As Double) As TaxRank

Dim taxIncome As Double

Dim rank As TaxRank

taxIncome = incomeAT - baseLine

If taxIncome <= 1455 Then

rank.index = 1

rank.rate = 0.03

rank.deduct = 0

ElseIf taxIncome > 1455 And taxIncome <= 4155 Then

rank.index = 2

rank.rate = 0.1

rank.deduct = 105

ElseIf taxIncome > 4155 And taxIncome <= 7755 Then

rank.index = 3

rank.rate = 0.2

rank.deduct = 555

ElseIf taxIncome > 7755 And taxIncome <= 27255 Then

rank.index = 4

rank.rate = 0.25

rank.deduct = 1005

ElseIf taxIncome > 27255 And taxIncome <= 41255 Then

rank.index = 5

rank.rate = 0.3

rank.deduct = 2755

ElseIf taxIncome > 41255 And taxIncome <= 57505 Then

rank.index = 6

rank.rate = 0.35

rank.deduct = 5505

ElseIf taxIncome > 57505 Then

rank.index = 7

rank.rate = 0.45

rank.deduct = 13505

End If

G_GetTaxRankByIncomeAT = rank

End Function

Private Function G_GetTaxRankByTax(tax As Double) As TaxRank

Dim rank As TaxRank

If tax <= 45 Then

rank.index = 1

rank.rate = 0.03

rank.deduct = 0

ElseIf tax > 45 And tax <= 345 Then

rank.index = 2

rank.rate = 0.1

rank.deduct = 105

ElseIf tax > 345 And tax <= 1245 Then

rank.index = 3

rank.rate = 0.2

rank.deduct = 555

ElseIf tax > 1245 And tax <= 7745 Then

rank.index = 4

rank.rate = 0.25

rank.deduct = 1005

ElseIf tax > 7745 And tax <= 13725 Then

rank.index = 5

rank.rate = 0.3

rank.deduct = 2755

ElseIf tax > 13725 And tax <= 22495 Then

rank.index = 6

rank.rate = 0.35

rank.deduct = 5505

Else

rank.index = 7

rank.rate = 0.45

rank.deduct = 13505

End If

G_GetTaxRankByTax = rank

End Function

'年终奖

Private Function N_GetBestYearAward(yearAward As Double, gongZi As Double, insure As Double, baseLine As Double)

Dim bestYearAward As Double

Dim taxIncome As Double

taxIncome = gongZi - insure - baseLine

taxIncome = yearAward + taxIncome

bestYearAward = yearAward '默认最佳年终奖

If taxIncome > 0 And taxIncome <= 19500 Then '1

If taxIncome <= 1500 Then

bestYearAward = 0

Else

bestYearAward = taxIncome - 1500

End If

ElseIf taxIncome > 19500 And taxIncome <= 31700 Then '2

bestYearAward = 18000

ElseIf taxIncome > 31700 And taxIncome <= 58500 Then '3

bestYearAward = taxIncome - 4500

ElseIf taxIncome > 58500 And taxIncome <= 128667 Then '4

bestYearAward = 54000

ElseIf taxIncome > 128667 And taxIncome <= 143000 Then '5

bestYearAward = taxIncome - 35000

ElseIf taxIncome > 143000 And taxIncome <= 195250 Then '6

bestYearAward = 108000

ElseIf taxIncome > 195250 And taxIncome <= 455000 Then '7

bestYearAward = taxIncome - 35000

ElseIf taxIncome > 455000 And taxIncome <= 628333.33 Then '8

bestYearAward = 420000

ElseIf taxIncome > 628333.33 And taxIncome <= 740000 Then '9

bestYearAward = taxIncome - 80000

ElseIf taxIncome > 740000 Then '10

bestYearAward = 660000

End If

N_GetBestYearAward = bestYearAward

End Function

Private Function NX_GetBestYearAward(yearIncome As Double)

Dim yearAward As Double

If yearIncome > 0 And yearIncome <= 60000 Then

yearAward = 0

ElseIf yearIncome > 60000 And yearIncome <= 125550 Then

yearAward = 18000

ElseIf yearIncome > 125550 And yearIncome <= 561000 Then

yearAward = 54000

ElseIf yearIncome > 561000 And yearIncome <= 669000 Then

yearAward = 108000

ElseIf yearIncome > 669000 And yearIncome <= 1494500 Then

yearAward = 420000

ElseIf yearIncome > 1494500 Then

yearAward = 660000

End If

NX_GetBestYearAward = yearAward

End Function

Private Function N_DIFF(gongZi As Double, insure As Double, baseLine As Double)

Dim diff As Double

Dim num As Double

num = gongZi - insure

If (gongZi < insure) Then

diff = -1

ElseIf (num > 0 And num < baseLine) Then

diff = baseLine - num

Else

diff = 0

End If

N_DIFF = diff

End Function

Private Function N_GetTaxIncome(yearAward As Double, gongZi As Double, insure As Double, baseLine As Double)

Dim num As Double

Dim taxIncome As Double

taxIncome = yearAward

num = (gongZi - insure) - baseLine

If num < 0 Then

taxIncome = yearAward + num

End If

N_GetTaxIncome = taxIncome

End Function

Private Function N_GetMaxYearAwardByIncomeAT(incomeAT As Double) As Double

Dim income As Double

If ((incomeAT > 16305) And (incomeAT <= 17460)) Then

income = (incomeAT - 105) / (1 - 0.1)

ElseIf ((incomeAT > 43755) And (incomeAT <= 48705)) Then

income = (incomeAT - 555) / (1 - 0.2)

ElseIf ((incomeAT > 82005) And (incomeAT <= 86955)) Then

income = (incomeAT - 1005) / (1 - 0.25)

ElseIf ((incomeAT > 296775) And (incomeAT <= 316005)) Then

income = (incomeAT - 2755) / (1 - 0.3)

ElseIf ((incomeAT > 434505) And (incomeAT <= 464775)) Then

income = (incomeAT - 5505) / (1 - 0.35)

ElseIf ((incomeAT > 541505) And (incomeAT <= 629505)) Then

income = (incomeAT - 13505) / (1 - 0.45)

End If

If (income <> Null) Then

income = Round(income, 2)

End If

income = Round(income, 2)

End Function

Private Function N_GetMinYearAwardByIncomeAT(incomeAT As Double) As Double

Dim income As Double

If (incomeAT <= 17460) Then

income = (incomeAT - 0) / (1 - 0.03)

ElseIf ((incomeAT > 17460) And (incomeAT <= 48705)) Then

income = (incomeAT - 105) / (1 - 0.1)

ElseIf ((incomeAT > 48705) And (incomeAT <= 86955)) Then

income = (incomeAT - 555) / (1 - 0.2)

ElseIf ((incomeAT > 86955) And (incomeAT <= 316005)) Then

income = (incomeAT - 1005) / (1 - 0.25)

ElseIf ((incomeAT > 316005) And (incomeAT <= 464775)) Then

income = (incomeAT - 2755) / (1 - 0.3)

ElseIf ((incomeAT > 464775) And (incomeAT <= 629505)) Then

income = (incomeAT - 5505) / (1 - 0.35)

Else

income = (incomeAT - 13505) / (1 - 0.45)

End If

income = Round(income, 2)

N_GetMinYearAwardByIncomeAT = income

End Function

Private Function N_GetYearAwardByTax(tax As Double) As Double

Dim income As Double

Dim average As Double

Dim rank As TaxRank

Dim newRank As TaxRank

income = 0

rank = N_GetTaxRankByTax(tax)

income = (tax + rank.deduct) / rank.rate

income = Round(income, 2)

average = income / 12

average = Round(average, 2)

newRank = G_GetTaxRank(average)

If newRank.rate <> rank.rate Then

income = -1

End If

N_GetYearAwardByTax = income

End Function

Private Function N_CALC(income As Double) As YearAwardInfo

Dim info As YearAwardInfo

Dim rank As TaxRank

Dim average As Double

Dim tax As Double

info.income = income

average = info.income / 12

average = Round(average, 2)

rank = G_GetTaxRank(average)

info.average = average

info.rate = rank.rate

info.deduct = rank.deduct

tax = info.income * info.rate - info.deduct

info.tax = Round(tax, 2)

info.incomeAT = info.income - info.tax

N_CALC = info

End Function

Private Function N_GetTaxRankByTax(tax As Double) As TaxRank

Dim rank As TaxRank

If tax <= 540 Then

rank.rate = 0.03

rank.deduct = 0

ElseIf tax > 540 And tax <= 5295 Then

rank.rate = 0.1

rank.deduct = 105

ElseIf tax > 5295 And tax < 21045 Then

rank.rate = 0.2

rank.deduct = 555

ElseIf tax > 21045 And tax < 103995 Then

rank.rate = 0.25

rank.deduct = 1005

ElseIf tax > 103995 And tax < 195245 Then

rank.rate = 0.3

rank.deduct = 2755

ElseIf tax > 195245 And tax <= 330495 Then

rank.rate = 0.35

rank.deduct = 5505

Else

rank.rate = 0.45

rank.deduct = 13505

End If

N_GetTaxRankByTax = rank

End Function

Private Function N_BLIND(income As Double) As Boolean

Dim blind As Boolean

blind = False

If (income > 18000 And income < 19283.33) Then

blind = True

ElseIf (income > 54000 And income < 60187.5) Then

blind = True

ElseIf (income > 108000 And income < 114600) Then

blind = True

ElseIf (income > 420000 And income < 447500) Then

blind = True

ElseIf (income > 660000 And income < 706538.46) Then

blind = True

ElseIf (income > 960000 And income < 1120000) Then

blind = True

End If

N_BLIND = blind

End Function

Private Function calc(incomeAT As Double, diff As Double, rate As Double, deduct As Double)

calc = Round((incomeAT - diff * rate - deduct) / (1 - rate), 2)

End Function

Private Sub N_AntiIncome(incomeAT As Double, diff As Double, rate As Double, deduct As Double, ByRef incomeMin As Double, ByRef incomeMax As Double)

Dim income As Double

Dim taxIncome As Double

Dim newIncomeAT As Double

income = calc(incomeAT, diff, rate, deduct)

taxIncome = income - diff

newIncomeAT = income - N_CALC(taxIncome).tax

If (Round(newIncomeAT) = Round(incomeAT)) Then

If incomeMin = 0 Then

incomeMin = income

Else

incomeMax = income

End If

End If

End Sub

Private Function N_GetIncomeByIncomeATWithGongZi(returnMin As Boolean, incomeAT As Double, gongZi As Double, insure As Double, baseLine As Double)

Dim diff As Double

Dim incomeMin As Double

Dim incomeMax As Double

incomeMin = 0

incomeMax = 0

diff = N_DIFF(gongZi, insure, baseLine)

Call N_AntiIncome(incomeAT, diff, 0.03, 0, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.1, 105, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.2, 555, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.25, 1005, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.3, 2755, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.35, 5505, incomeMin, incomeMax)

Call N_AntiIncome(incomeAT, diff, 0.45, 13505, incomeMin, incomeMax)

If (returnMin) Then

N_GetIncomeByIncomeATWithGongZi = IIf(incomeMin = 0, "", incomeMin)

Else

N_GetIncomeByIncomeATWithGongZi = IIf(incomeMax = 0, "", incomeMax)

End If

End Function

'劳务

Private Function L_CALC(income As Double) As LabourInfo

Dim info As LabourInfo

Dim fee As Double

Dim tax As Double

Dim rank As TaxRank

info.income = income

If info.income <= 800 Then

info.taxIncome = 0

info.rate = 0

info.deduct = 0

info.tax = 0

info.incomeAT = info.income

Else

fee = 800

If info.income > 4000 Then

fee = info.income * 0.2

End If

info.fee = Round(fee, 2)

info.taxIncome = info.income - info.fee

rank = L_GetTaxRank(info.taxIncome)

info.rate = rank.rate

info.deduct = rank.deduct

tax = info.taxIncome * info.rate - info.deduct

info.tax = Round(tax, 2)

info.incomeAT = info.income - info.tax

End If

L_CALC = info

End Function

Private Function L_GetIncomeByTax(tax As Double) As Double

Dim income As Double

If tax <= 640 Then

income = tax / 0.2 + 800

ElseIf tax > 640 And tax <= 4000 Then

income = tax / (0.8 * 0.2)

ElseIf tax > 4000 And tax <= 13000 Then

income = (tax + 2000) / (0.8 * 0.3)

Else

income = (tax + 7000) / (0.8 * 0.4)

End If

income = Round(income, 2)

L_GetIncomeByTax = income

End Function

Private Function L_GetIncomeByIncomeAT(incomeAT As Double) As Double

Dim income As Double

If incomeAT <= 800 Then

income = incomeAT

ElseIf incomeAT > 800 And incomeAT <= 3360 Then

income = (incomeAT - 160) / 0.8

ElseIf incomeAT > 3360 And incomeAT <= 21000 Then

income = incomeAT / (1 - 0.8 * 0.2)

ElseIf incomeAT > 21000 And incomeAT <= 49500 Then

income = (incomeAT - 2000) / (1 - 0.8 * 0.3)

Else

income = (incomeAT - 7000) / (1 - 0.8 * 0.4)

End If

income = Round(income, 2)

L_GetIncomeByIncomeAT = income

End Function

Private Function L_GetTaxRank(taxIncome As Double) As TaxRank

Dim rank As TaxRank

If taxIncome <= 20000 Then

rank.index = 1

rank.rate = 0.2

rank.deduct = 0

ElseIf taxIncome > 20000 And taxIncome <= 50000 Then

rank.index = 2

rank.rate = 0.3

rank.deduct = 2000

Else

rank.index = 3

rank.rate = 0.4

rank.deduct = 7000

End If

L_GetTaxRank = rank

End Function