基于Excel财务数据变化时计算个人所得税
发布时间：2015-07-09 11:04
摘 要 介绍了当财务数据变化时计算个人所得税的三种方法，即按照税务部门规定的相关税率计算个人所得税、使用函数法巧算个人所得税、使用排序分段核算法计算个人所得税。以上每一种方法都有一定的灵活性，可以减轻税务部门工作人员的劳动强度，提高工作效率。
关键词 个人所得税 税率 VLOOKUP函数 速算扣除数
在国民经济普遍提高的今天，有相当一部分人每月都需要缴纳个人所得税，尤其是在不扣税基数可能变化的情况下。特别是当一些单位由于人数多，其工资档次参差不齐，个人所得税九级累进税率计算过程更显得繁锁，耗时又易错。我们可以利用Office2000中的Ex?鄄cel2000软件计算个人所得税。通过运用Excel中的公式和多种函数，只要将每月个人收入输入到表格中，应纳税所得额、税率、速算扣除数及应纳税额就会自动生成，我们可以灵活改变各扣税分段界限值及其扣税率等方法计算个人所得税，充分利用Excel强大的表格数据处理功能，使个人所得税的计算准确、简单、易行。
1 按照国家税务部门规定的相关税率计算个人所得税
首先根据工资额和纳税基数看所得税算法，因为不同的应税所得额有不同的税率和速算扣除数，其他复杂的情况都可在此方法的基础上变化得到:当月应纳税所得额=当月计税总额－减除费用－附加减除费用。
以X代表应缴税(减去免税基数)的工薪(这里的个人所得税仅以工薪为例),
Tax代表应缴所得税，那么：
当500＜X≤2 000,则TAX=(X-500)×10%+500×5%=﹥TAX=X×10-25
当2 000＜X≤5 000, 则TAX = (X-
2 000)×15%+(2 000-500)×10%+500×5%
=﹥TAX=X×15-125
……
根据《税法》规定依此类推，计算个人所得税的通用公式为：个人每月应纳所得税=个人每月应纳税所得额 粮梅段奥剩偎憧鄢?
因此，扣除数=上一范围上限×本范围税率-上一范围上限×上一范围税率＋上一范围扣除数。
例1：某人将自有机器一台，转让给另一个人，取得价款15 000元，减除机器原值及同时发生的拆迁费、运输费8 800元，其应缴纳多少个人所得税？
应纳税额 = (15 000-8 800)×20% =
1 240(元)。
上述公式说明：个人每月应纳税所得额=月工资额-非纳税项目(如住房公积金等)-800（元）。
2 使用函数法巧算个人所得税
了解了应税额的计算方法，具体计算操作
2.1 用函数法巧算个人所得税
（1）充分利用Excel中IF函数嵌套功能，可使上面问题得到解决，假设L列为“应纳税所得额”，M列为“应纳个人所得税”，在M列输入公式(假设首位人员应纳税所得额位置L2)：IF(L2500，L2×0.05， IF ( L22 000， L2×0.1-25， IF (L2
5 000，L2×0.15-125，IF(L220 000，2×0.2-375，IF(L240 000，L2×0.25-1 375，IF (L2 60 000， L2×0.3-3375， IF(L2 80 000，L2×0.35-6 375， IF( L2100 000， L2×0.4-
10 375，L2×0.45-15 375))))))))
确认后，得出首位人员应纳个人所得税，拖动填充句柄，则所有人员应纳税额全部出来。该公式说明：①公式虽复杂，但只要一次输入即可自动完成全部计算；②此公式在计算个人所得税时可作为固定公式运用；③根据单位人员工资档次情况，可简化该公式。
根据税务部门要求，认真贯彻《中华人民共和国个人所得税法》，做好个人所得税征缴和代扣代缴工作，现将在职人员个人所得税税率表(工资、薪金所得适用)予以公示。
（2）当月计税总额是指当月全部工资、薪金、讲课费、加班费、各项补贴、劳务等扣除公积金、社保支出后的余额。
（3）当月应纳税所得额是指当月计税总额扣除减除费用、附加减除费用后的余额。
（4）离退休人员的工资、国家各项政策性补贴不计税；其他部分不超过4 000元的减除费用800元后按20%征收，超过4 000元的减除20%的费用后按20%征收。
（5）外聘教师、其他劳务人员当月在本院各项不超过4 000元的减除费用800元后按20%征收，超过4 000元的减除20%的费用后按20%征收。
例2：在职教师王某10月份：①工资应发数为1 850元(另扣公积金140元、社保支出40元)；②讲课费1 200元；③校内津贴900元；④加班费200元；⑤其他劳务700元。则王某当月计税总额为1 850-140-40+1 200+900+200+700=4 670元；其当月应纳税所得额为4 670-800-400=3 470元(800为全国统一减除费用额， 400 计税方法1：
3 470元分为三级，500+1 500+1 470；第1级税款为500×5%=25元；第2级税款为1 500×10%=150元；第3级税款为1 470×15%=220.5元；王某应缴税款合计数为25+150+220.5=395.5元。
计税方法2：
3 470×15%-125=395.5元(125为3 470元所在第三级的速算扣除数)；王某应缴税款395.5元。
2.2 常规方法计算个人所得税
（1）常规方法计算。扣除数=应纳税范围下限×本范围税率－应纳税范围下限×上－范围税率＋上一范围扣除数。
那么公式为(以E3为例)，E3=B3×D3-B3×D2+E2。E4～E10：根据E3填充得到。
（2）G4～G7分别输入：“工资总额”、“纳税基数”、“应纳税额”、“个人所得税”四项。应纳税额等于“工资总额”减去“纳税基数”，如果“工资总额”小于“纳税基数”，那么应纳税额为0，在H6=IF(H4－H5＞0，HE－H5，0)。
（3）选择“插入”｜“名称”｜“定义”，在定义名称对话框输入max，在引用位置中输入VLOOKUP($H$6,$B$2:$B$10,1),查阅应纳税额属于哪个扣税范围。
（4）选择H7=H6×VLOOKUP(max，$B$2:$E$10，3)－VLOOKUP(max，$B$2:$E$10，4)。
分析公式：VLOOKUP(max,$B$2:$E$10,3)查阅该扣税范围扣税税率；VLOOKUP(max,$B$2:$E$10,4)查阅该扣税范围应减的扣除数。
（5）在“工资总额”和“纳税基数”中输入数值，即可得到个人所得税额，也可以改变各应纳税范围的上限和下限以及税率。由于个人所得的税率分为九级，如果使用IF嵌套的方式非常复杂，而且容易出错，且一旦税率发生变化或税率等级增加，不宜修改。这里使用VLOOKUP函数来计算。
选择工作表Sheet2，更名为“tax”，然后输入所需税率及扣除数。其中，扣除数一列可以直接输入，也可以通过公式计算。那么公式为(以E3为例)，E3=C3×D3－C3×D2＋E2。E4～E10：根据E3填充得到。
计算个人所得税。选择单元格L2，输入公式-(K2×VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1),tax!$C$2:$E410,2)－VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1)tax!$C$2:$E$10,3))。
由于这里引用工作表tax中固定的单元格，所以使用地址的绝对引用，这样在后面用自动填充功能复制公式时，所引用的tax中的单元格地址才不会改变。公式前面加一个“-”(负号)，是因为在这个工资表中，所有应扣除项都是用负数表示。
分析公式，其中：VLOOKUP(K2,tax!$C$2:$C10,1) 查阅应纳税额属于哪个扣税范围；VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1),tax!$C$2:$E410,2)查阅该扣税范围和扣税率；VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1)tax!$C$2:$E$10,3)查阅该扣税范围应减的扣税数。
利用自动填充功能，快速求出了所有职工的个人所得税。
最后计算实发工资，实发工资应为“合计应发”减去“个人所得税”后的数值，即在M2=J2+L2, 然后利用自动填充功能，得到所有职工的实发工资。
2.3 实际代扣个人所得税时使用“取整”函数的方法
计算的个人所得税额经常带有角、分单位，给实际发放工资时代扣个人所得税带来很多不便。如利用电话费的缴费方法，将使代扣个人所得税操作简便。
这一方法主要是利用取整函数ROUND()四舍五入，舍角分取元为单位，其多交或少交的差额在下次抵补，累计计算。计算时需增加“上次结余”、“累计应代扣额”、“实际代扣额”、“本次结余”四个栏目，其中“上次结余”是反映上次多扣或少扣的金额。
3 使用排序分段核算法计算个人所得税
在工资表中只要增加“应纳税额”和“应纳个人所得税”两项。
（1）计算应纳税所得额。对应发工资，薪金减除800元和扣除项目后，得出“应纳税所得额”。
（2）排序。将“应纳税所得额”应用Excel排序功能进行从小到大排序处理。
（3）输入公式。按适用税率和速算扣除数分段输入对应公式进行核算得出“应纳个人所得税”。
例如：对某单位“应纳税所得额”从小到大排序后先计算500元以内人员税额(假设首位人员应纳税所得额位置在L2)，对其“应纳个人所得税”列输入公式L2×0.05,确认后得出首位人员应纳税额，利用Excel智能填充功能，拖动填充句柄，则其他500元以内人员应纳税额全部自动计算出来， 同样方法再处理500～2 000元部分人员应纳税额，依此类推分段核算出所有人员应纳个人所得税额。
排序分类法公式输入简单，核算清楚，但它的缺点是必须多次输入公式，而且打乱了单位人员及机构次序。
关键词 个人所得税 税率 VLOOKUP函数 速算扣除数
在国民经济普遍提高的今天，有相当一部分人每月都需要缴纳个人所得税，尤其是在不扣税基数可能变化的情况下。特别是当一些单位由于人数多，其工资档次参差不齐，个人所得税九级累进税率计算过程更显得繁锁，耗时又易错。我们可以利用Office2000中的Ex?鄄cel2000软件计算个人所得税。通过运用Excel中的公式和多种函数，只要将每月个人收入输入到表格中，应纳税所得额、税率、速算扣除数及应纳税额就会自动生成，我们可以灵活改变各扣税分段界限值及其扣税率等方法计算个人所得税，充分利用Excel强大的表格数据处理功能，使个人所得税的计算准确、简单、易行。
1 按照国家税务部门规定的相关税率计算个人所得税
首先根据工资额和纳税基数看所得税算法，因为不同的应税所得额有不同的税率和速算扣除数，其他复杂的情况都可在此方法的基础上变化得到:当月应纳税所得额=当月计税总额－减除费用－附加减除费用。
以X代表应缴税(减去免税基数)的工薪(这里的个人所得税仅以工薪为例),
Tax代表应缴所得税，那么：
当500＜X≤2 000,则TAX=(X-500)×10%+500×5%=﹥TAX=X×10-25
当2 000＜X≤5 000, 则TAX = (X-
2 000)×15%+(2 000-500)×10%+500×5%
=﹥TAX=X×15-125
……
根据《税法》规定依此类推，计算个人所得税的通用公式为：个人每月应纳所得税=个人每月应纳税所得额 粮梅段奥剩偎憧鄢?
因此，扣除数=上一范围上限×本范围税率-上一范围上限×上一范围税率＋上一范围扣除数。
例1：某人将自有机器一台，转让给另一个人，取得价款15 000元，减除机器原值及同时发生的拆迁费、运输费8 800元，其应缴纳多少个人所得税？
应纳税额 = (15 000-8 800)×20% =
1 240(元)。
上述公式说明：个人每月应纳税所得额=月工资额-非纳税项目(如住房公积金等)-800（元）。
2 使用函数法巧算个人所得税
了解了应税额的计算方法，具体计算操作
2.1 用函数法巧算个人所得税
（1）充分利用Excel中IF函数嵌套功能，可使上面问题得到解决，假设L列为“应纳税所得额”，M列为“应纳个人所得税”，在M列输入公式(假设首位人员应纳税所得额位置L2)：IF(L2500，L2×0.05， IF ( L22 000， L2×0.1-25， IF (L2
5 000，L2×0.15-125，IF(L220 000，2×0.2-375，IF(L240 000，L2×0.25-1 375，IF (L2 60 000， L2×0.3-3375， IF(L2 80 000，L2×0.35-6 375， IF( L2100 000， L2×0.4-
10 375，L2×0.45-15 375))))))))
确认后，得出首位人员应纳个人所得税，拖动填充句柄，则所有人员应纳税额全部出来。该公式说明：①公式虽复杂，但只要一次输入即可自动完成全部计算；②此公式在计算个人所得税时可作为固定公式运用；③根据单位人员工资档次情况，可简化该公式。
根据税务部门要求，认真贯彻《中华人民共和国个人所得税法》，做好个人所得税征缴和代扣代缴工作，现将在职人员个人所得税税率表(工资、薪金所得适用)予以公示。
（2）当月计税总额是指当月全部工资、薪金、讲课费、加班费、各项补贴、劳务等扣除公积金、社保支出后的余额。
（3）当月应纳税所得额是指当月计税总额扣除减除费用、附加减除费用后的余额。
（4）离退休人员的工资、国家各项政策性补贴不计税；其他部分不超过4 000元的减除费用800元后按20%征收，超过4 000元的减除20%的费用后按20%征收。
（5）外聘教师、其他劳务人员当月在本院各项不超过4 000元的减除费用800元后按20%征收，超过4 000元的减除20%的费用后按20%征收。
例2：在职教师王某10月份：①工资应发数为1 850元(另扣公积金140元、社保支出40元)；②讲课费1 200元；③校内津贴900元；④加班费200元；⑤其他劳务700元。则王某当月计税总额为1 850-140-40+1 200+900+200+700=4 670元；其当月应纳税所得额为4 670-800-400=3 470元(800为全国统一减除费用额， 400 计税方法1：
3 470元分为三级，500+1 500+1 470；第1级税款为500×5%=25元；第2级税款为1 500×10%=150元；第3级税款为1 470×15%=220.5元；王某应缴税款合计数为25+150+220.5=395.5元。
计税方法2：
3 470×15%-125=395.5元(125为3 470元所在第三级的速算扣除数)；王某应缴税款395.5元。
2.2 常规方法计算个人所得税
（1）常规方法计算。扣除数=应纳税范围下限×本范围税率－应纳税范围下限×上－范围税率＋上一范围扣除数。
那么公式为(以E3为例)，E3=B3×D3-B3×D2+E2。E4～E10：根据E3填充得到。
（2）G4～G7分别输入：“工资总额”、“纳税基数”、“应纳税额”、“个人所得税”四项。应纳税额等于“工资总额”减去“纳税基数”，如果“工资总额”小于“纳税基数”，那么应纳税额为0，在H6=IF(H4－H5＞0，HE－H5，0)。
（3）选择“插入”｜“名称”｜“定义”，在定义名称对话框输入max，在引用位置中输入VLOOKUP($H$6,$B$2:$B$10,1),查阅应纳税额属于哪个扣税范围。
（4）选择H7=H6×VLOOKUP(max，$B$2:$E$10，3)－VLOOKUP(max，$B$2:$E$10，4)。
分析公式：VLOOKUP(max,$B$2:$E$10,3)查阅该扣税范围扣税税率；VLOOKUP(max,$B$2:$E$10,4)查阅该扣税范围应减的扣除数。
（5）在“工资总额”和“纳税基数”中输入数值，即可得到个人所得税额，也可以改变各应纳税范围的上限和下限以及税率。由于个人所得的税率分为九级，如果使用IF嵌套的方式非常复杂，而且容易出错，且一旦税率发生变化或税率等级增加，不宜修改。这里使用VLOOKUP函数来计算。
选择工作表Sheet2，更名为“tax”，然后输入所需税率及扣除数。其中，扣除数一列可以直接输入，也可以通过公式计算。那么公式为(以E3为例)，E3=C3×D3－C3×D2＋E2。E4～E10：根据E3填充得到。
计算个人所得税。选择单元格L2，输入公式-(K2×VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1),tax!$C$2:$E410,2)－VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1)tax!$C$2:$E$10,3))。
由于这里引用工作表tax中固定的单元格，所以使用地址的绝对引用，这样在后面用自动填充功能复制公式时，所引用的tax中的单元格地址才不会改变。公式前面加一个“-”(负号)，是因为在这个工资表中，所有应扣除项都是用负数表示。
分析公式，其中：VLOOKUP(K2,tax!$C$2:$C10,1) 查阅应纳税额属于哪个扣税范围；VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1),tax!$C$2:$E410,2)查阅该扣税范围和扣税率；VLOOKUP(VLOOKUP(K2,tax!$C$2:$C10,1)tax!$C$2:$E$10,3)查阅该扣税范围应减的扣税数。
利用自动填充功能，快速求出了所有职工的个人所得税。
最后计算实发工资，实发工资应为“合计应发”减去“个人所得税”后的数值，即在M2=J2+L2, 然后利用自动填充功能，得到所有职工的实发工资。
2.3 实际代扣个人所得税时使用“取整”函数的方法
计算的个人所得税额经常带有角、分单位，给实际发放工资时代扣个人所得税带来很多不便。如利用电话费的缴费方法，将使代扣个人所得税操作简便。
这一方法主要是利用取整函数ROUND()四舍五入，舍角分取元为单位，其多交或少交的差额在下次抵补，累计计算。计算时需增加“上次结余”、“累计应代扣额”、“实际代扣额”、“本次结余”四个栏目，其中“上次结余”是反映上次多扣或少扣的金额。
3 使用排序分段核算法计算个人所得税
在工资表中只要增加“应纳税额”和“应纳个人所得税”两项。
（1）计算应纳税所得额。对应发工资，薪金减除800元和扣除项目后，得出“应纳税所得额”。
（2）排序。将“应纳税所得额”应用Excel排序功能进行从小到大排序处理。
（3）输入公式。按适用税率和速算扣除数分段输入对应公式进行核算得出“应纳个人所得税”。
例如：对某单位“应纳税所得额”从小到大排序后先计算500元以内人员税额(假设首位人员应纳税所得额位置在L2)，对其“应纳个人所得税”列输入公式L2×0.05,确认后得出首位人员应纳税额，利用Excel智能填充功能，拖动填充句柄，则其他500元以内人员应纳税额全部自动计算出来， 同样方法再处理500～2 000元部分人员应纳税额，依此类推分段核算出所有人员应纳个人所得税额。
排序分类法公式输入简单，核算清楚，但它的缺点是必须多次输入公式，而且打乱了单位人员及机构次序。
下一篇：动态哈夫曼编码的改进