手把手教你用EXCEL计算新个人所得税

Police_Hlj 2018-12-25

展开全文

新个人所得税累计预扣预缴方法出来后，大家都在关心新的个税如何计算，本文为大家整理如何在EXCEL中利用函数和公式计算累计预扣预缴模式下如何计算当月应缴纳个人所得税的模型。

个人所得税的计算原理

任何模型构建前，先要弄懂原理。

新个人所得税法下，《关于全面实施新个人所得税法若干征管衔接问题的公告》（国家税务总局公告2018年第56号中）对居民个人预扣预缴方法做出了明确的规定，由单位预扣预缴，居民个人次年办理综合所得年度汇算清缴，税款多退少补。

其中，关于工资薪金部分的预扣预缴方法为：

（1）本期应预扣预缴税额=（累计预扣预缴应纳税所得额×预扣率-速算扣除数)-累计减免税额-累计已预扣预缴税额；

（2）累计预扣预缴应纳税所得额=累计收入-累计免税收入-累计减除费用-累计专项扣除-累计专项附加扣除-累计依法确定的其他扣除；

其中：累计减除费用，按照5000元/月乘以纳税人当年截至本月在本单位的任职受雇月份数计算。

（3）上述公式中，计算居民个人工资、薪金所得预扣预缴税额的预扣率、速算扣除数，按《个人所得税预扣率表一》(见下图）执行。

什么是减除费用？仅限支付工资、薪金所得时填写。具体按税法规定的减除费用标准填写。如，2019年为5000元/月。

什么是专项扣除？分别填写按规定允许扣除的基本养老保险费、基本医疗保险费、失业保险费、住房公积金的金额。即'三险一金'

什么是其他扣除？按规定允许扣除的年金（包括企业年金、职业年金）、商业健康保险、税延养老保险、及其他扣除项目的累计金额。

什么是专项附加扣除？纳税人按规定可享受的子女教育、赡养老人、住房贷款利息或住房租金、继续教育扣除的累计金额。大病医疗扣除由纳税人在年度汇算清缴时办理，预扣预缴时无需填报。

手把手教你用EXCEL计算新个人所得税

EXCEL模型构建

基础原理学会后，就开始我们到EXCEL中去实现吧。

由于新个人所得税法是按照累计预扣减去已经预缴的数据来计算当期的应预扣预缴个税，因此模型在框架上要分成三部分来算，即当月预扣预缴应纳税所得额、累计预扣预缴应纳税所得额、税款计算三部分。

在EXCEL模型中，我们用白色区域（不填充底色）表示手动输入的数据，如当月收入、当月可扣除项目。用灰色区域表示公式部分，表示此部分区域不能修改。用绿色表示输出结果。

第一部分：当月应纳税所得额的计算

当月预扣预缴应纳税所得额=当月收入-当月免税收入-当月减除费用-当月专项扣除-当月专项附加扣除-当月依法确定的其他扣除。

第一步先在EXCEL中到搭好框架，公示区域填充灰色底色，需要手动填写数据的部分不填充颜色。

手把手教你用EXCEL计算新个人所得税

当月应纳税所得额的计算

第4-9行(序号A-F，当月收入和扣除项目)：均按照当月的实际情况进行填写。

第10行(序号G=A-B-C-D-E-F，当月预扣预缴应纳税所得额)：在D10单元格输入公式=MAX(D4-SUM(D5:D9),0)，并拖动到23月（第O列)

用MAX函数，当月收入小于当月扣除金额时，当月应纳税所得额出现负数时显示为0。max函数以及后面用到的其他函数用法解释在第五部分详细说明。

其中序号A行'当月收入'，可以根据实际情况进行扩展，如补充基本工资、奖金、加班费等，用SUM函数进行集成；序号C行'当月减除项目'是指三险一金，也可以进行扩展，列出三险一金的明细。

第二部分：累计应纳税所得额的计算

累计部分项目为对第一部分的当月数据进行求和。先搭好架子，将需要计算的参数（同当月数据，只是当月改成累计）先设定好，参考如下截图。

手把手教你用EXCEL计算新个人所得税

累计应纳税所得额的计算

第13-18行（序号H-M，累计收入和累计扣除项目)需：在D13单元格输入公式：=SUM($D4:D4)，然后将公式向又复制或拖动到12月（第O列)，向下复制或拖动到第18行。

第19行（序号N，累计预扣预缴应纳税所得额)：可以直接复制第13行第公式，也可以在D19单元格输入=MAX(D13-SUM(D14:D18),0)，复制或拖动到O19单元格。

由于这一部分都是公式，填充灰色表示。

第三部分：税款计算

税款计算公式如下：

累计应预扣预缴税额=（累计预扣预缴应纳税所得额×预扣率-速算扣除数）-累计减免税额

本期应预扣预缴税额=累计应预扣预缴税额 - 累计已预扣预缴税额

还是先搭框架，如下图：

手把手教你用EXCEL计算新个人所得税

税款的计算

第22行（序号N，累计预扣预缴应纳税所得额），本例中为了方便，将第二部分计算的累计预扣预缴应纳税所得额直接应用下来，在D22单元格输入：=D19，并将公式向后复制或拖动到O22单元格。

第23-26行(序号O-R，预扣率、速算扣除数、税额)与第27行（序号S，累计应预扣预缴税额)是等效的，可以任意选择一种方式即可。

第23行（序号O，预扣率）的计算：D23单元格输入：=MAX(LOOKUP(D$22,{0,3.6,14.4,30,42,66,96}*10000,{0.03,0.1,0.2,0.25,0.3,0.35,0.45}),0)，将公式向后复制或拖动到O23单元格。

第24行（序号P，速算扣除数）的计算：D24单元格输入：=LOOKUP(D$22,{0,3.6,14.4,30,42,66,96}*10000,{0,2520,16920,31920,52920,85920,181920})，将公式向后复制或拖动到O24单元格。

第25行（序号Q，累计减免税额），结合实际情况手动输入，不填充底色。

第26行（序号R，累计应预扣预缴税额）的计算：D26单元格输入：=D22*D23-D24-D25，将公式向后复制或拖动到O26单元格。

第27行（序号S，累计应预扣预缴税额），这一行是第二种方法计算税额，与序号O-R过程是等效的，大家在自己做的时候，选其中一种即可。在D27单元格输入：=ROUND(MAX(D22*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,2520,16920,31920,52920,85920,181920},0)-D25,2)，将公式向后复制或拖动到O27单元格。

第28行（序号T，累计已预扣预缴税额)，这一行是计算截止上月累计已预缴税额，所以1月份的时候为0，2月的数=1月的数，3月的数=（1月+2月）的数，4月的数=（1月+2月+3月）的数....以此类推。所以D28单元格=0，在E28单元格输入：=SUM($D29:D29)，将公式向后复制或拖动到O28单元格。$是绝对引用，$D29表示固定第D列，行数随着公式的移动变化。

第29行（序号U，本期应预扣预缴税额），在D29单元格输入：=D27-D28，将公式向后复制或拖动到O29单元格。

美化：序号S行是计算的累计的个人所得税，序号U行是本期应交的个人所得税，这两行是我们需要的结果，我们填充绿色表示。序号Q行减免税额手动输入，不填充底色。其他区域全部都是公式和中间过程，用填充灰色。

这样，我们一个计算每月应该交多少个人所得税，以及全年已经交了多少个人所得税的模型就构建完成了。完整的模型如下

手把手教你用EXCEL计算新个人所得税

完整的excel模型

第四部分：设置保护

我们做完模型后，会分享给其他人，为了避免在后续使用的过程中不小心修改公式导致模型出错，我们可以对模型设置保护。接下来教大家怎么对公式部分设置保护，而需要手动填写的部分仍然可以正常填写。

第1步：取消单元格锁定。选上可以手动修改的区域(本例中D4:O9区域，以及D25:O25区域），右键-【设置单元格格式】-【保护】-取消'锁定'勾选。

手把手教你用EXCEL计算新个人所得税

第2步：保护单元格。菜单栏【审阅】-【保护工作表】，弹出保护工作表对话框，可以设置保护密码（也可以不设置，看个人需求）。勾选'选定锁定单元格'。

测试一下，是不是第1步中取消单元格锁定的区域可以编辑，而其他区域则不能编辑呢？

手把手教你用EXCEL计算新个人所得税

如果要取消保护也很简单，【审阅】-【撤销工作表保护】就可以啦

第五部分：公式解释

MAX函数

MAX(数值1,数值2,...)，返回一组值中的最大值。我们在D9单元格中运用MAX()函数，可以实现当计算出的'当月预扣预缴应纳税所得额'小于0时，显示值为0，大于0时，显示计算结果。

数组{}用法

EXCEL公式中的数组用大括号表示{},{0,3.6,14.4,30,42,66,96}*10000，代表数组中的每一个值都和10000相乘，得到{0,36000,144000,300000,4200000,660000,960000}，即应纳税所得额的7级级距，{0.03,0.1,0.2,0.25,0.3,0.35,0.45}则表示了3%-45%的7级税率。

LOOKUP函数

LOOKUP（要查找的值,要查找的区域,要查找的结果），要查找的区域和要查找的结果都是一个向量（一组数据），二者构成一个二维的数组。本例中，LOOKUP(D$22,{0,3.6,14.4,30,42,66,96}*10000,{0.03,0.1,0.2,0.25,0.3,0.35,0.45}，查找应纳税所得额的级数对应的税率，当D22单元格是8760元时，公式向后查找，小于3.6万元对应的税率在第1档即税率为3%；如果D22单元格等于22.66万元时，公式查找位于14.4-30万元之间对应的税率是20%，依此类推。