下载文档原格式
Excel个人所得税公式和自定义函数注意：本文方法整理自网络用Excel 计算个人所得税是大家经常遇到的事情，一般我都是用自定义函数来实现。
其实用公式来计算个人所得税也是很不错的选择，这里综合网上的方法，分别介绍用公式和自定义函数来计算个人所得税。
一、使用公式计算个人所得税使用以下公式即可计算个人所得税：=ROUND(MAX((A1-3500)*0.05*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)其中的 A1 为需要计算所得税的金额，效果如图：二、使用自定义函数下面我们就来介绍一下使用自定义函数来计算所得税，1、创建一个计算所得税的自定义函数：打开 VBE，添加一个自定义模块，在模块中添加以下代码：Public Function Incometax(Amount)Select Case AmountCase Is <= 3500Incometax = 0Case 3500 To 5000Incometax = 0.03 * (Amount - 3500) - 0Case 5000.01 To 8000Incometax = 0.1 * (Amount - 3500) - 105Case 8000.01 To 12500Incometax = 0.2 * (Amount - 3500) - 555Case 12500.01 To 38500Incometax = 0.25 * (Amount - 3500) - 1005Case 38500.01 To 58500Incometax = 0.3 * (Amount - 3500) - 2755Case 58500.01 To 83500Incometax = 0.35 * (Amount - 3500) - 5505Case ElseIncometax = 0.45 * (Amount - 3500) - 13505End SelectEnd Function2、在工作表中直接使用以下公式即可：=Incometax(A1)其中的 A1 为需要计算所得税的金额，效果如图：这两种方法，各有优缺点，各位可以根据需要自行采用。
79000.004947.208000.00审批人：制表人：XXXXXXX公司2023年1月工资发放表工资所属期：2022年12月份72793.3479000.004947.208000.00审批人：制表人：XXXXXXX公司2023年2月工资发放表工资所属期：2023年1月份73529.5379000.004947.208000.00审批人：制表人：XXXXXXX公司2023年3月工资发放表工资所属期：2023年2月份73529.5279000.004947.208000.00审批人：制表人：XXXXXXX公司2023年4月工资发放表工资所属期：2023年3月份73529.5979000.004947.208000.00审批人：制表人：XXXXXXX公司2023年5月工资发放表工资所属期：2023年4月份73529.5279000.004947.208000.00审批人：制表人：XXXXXXX公司2023年6月工资发放表工资所属期：2023年5月份73529.5279000.004947.208000.00审批人：制表人：XXXXXXX公司2023年7月工资发放表工资所属期：2023年6月份73529.5279000.004947.208000.00审批人：制表人：XXXXXXX公司2023年8月工资发放表工资所属期：2023年7月份73529.5379000.004947.208000.00审批人：制表人：XXXXXXX公司2023年9月工资发放表工资所属期：2023年8月份73144.8879000.004947.208000.00审批人：制表人：XXXXXXX公司2023年10月工资发放表工资所属期：2023年9月份72926.8379000.004947.208000.00审批人：制表人：XXXXXXX公司2023年11月工资发放表工资所属期：2023年10月份72901.7179000.004947.208000.00审批人：制表人：XXXXXXX公司2023年12月工资发放表工资所属期：2023年11月份72695.41。
应发薪资序号姓名身份证号部门入职时间基本薪资岗位津贴工龄薪资全勤奖绩效奖共计加班补贴00016,000.00500.006,500.0000027,000.00500.007,500.0000038,000.00500.008,500.0000049,000.00500.009,500.00000510,000.00500.0010,500.00000611,000.00500.0011,500.00000712,000.00500.0012,500.00000813,000.00500.0013,500.00000914,000.00500.0014,500.00001015,000.00500.0015,500.00001116,000.0016,000.00001217,000.0017,000.00001318,000.0018,000.00001419,000.0019,000.00001520,000.0020,000.00001621,000.0021,000.00001722,000.0022,000.00001823,000.0023,000.00001924,000.0024,000.00002025,000.0025,000.00002126,000.0026,000.00002227,000.0027,000.00002328,000.0028,000.00002429,000.0029,000.00002530,000.0030,000.00002631,000.0031,000.00002732,000.0032,000.00002833,000.0033,000.00002934,000.0034,000.00003035,000.0035,000.00003136,000.0036,000.00加项应扣薪资通信补贴过节费共计应出勤天实质出勤告假扣款迟到扣款共计数天数-19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00---19.0019.00--收入今年截止先期累本期收入今年累计收社保基数计收入入30,000.006,500.0036,500.004,000.00 34,500.007,500.0042,000.004,000.00 39,000.008,500.0047,500.004,000.00 43,500.009,500.0053,000.004,000.00 48,000.0010,500.0058,500.004,000.00 52,500.0011,500.0064,000.004,000.00 57,000.0012,500.0069,500.004,000.00 61,500.0013,500.0075,000.004,000.00 66,000.0014,500.0080,500.004,000.00 70,500.0015,500.0086,000.004,000.00 73,000.0016,000.0089,000.004,000.00 77,500.0017,000.0094,500.004,000.00 82,000.0018,000.00100,000.004,000.00 86,500.0019,000.00105,500.004,000.00 91,000.0020,000.00111,000.004,000.00 95,500.0021,000.00116,500.004,000.00 100,000.0022,000.00122,000.004,000.00 104,500.0023,000.00127,500.004,000.00 109,000.0024,000.00133,000.004,000.00 113,500.0025,000.00138,500.004,000.00 118,000.0026,000.00144,000.004,000.00 122,500.0027,000.00149,500.004,000.00 127,000.0028,000.00155,000.004,000.00 131,500.0029,000.00160,500.004,000.00 136,000.0030,000.00166,000.004,000.00 140,500.0031,000.00171,500.004,000.00 145,000.0032,000.00177,000.004,000.00 149,500.0033,000.00182,500.004,000.00 154,000.0034,000.00188,000.004,000.00 158,500.0035,000.00193,500.004,000.00 163,000.0036,000.00199,000.004,000.00养老保险基本医疗保险社保基数单位单位（ 16%）个人（ 8%）个人（ 2%）（6.5%）640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.00640.00320.004,000.00260.0080.002019版最新个人所得税计算表( 自动生成薪资表薪资条)六险一金工伤保险失业保险生育保险重病医疗保共计住宅公积金今年从前累险住宅基数单位个人单位单位(0.1%)单位（ 1%）单位个人单位(12%)个人(12%)养老保险（0.6%）（0.4%）（0.8%）4.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.001,664.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.001,664.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.001,664.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.004.0024.0016.0032.0040.001,000.00416.004,000.00480.00480.002,080.00条)专项扣除额从前累计本期今年累计今年截止上期累计住宅公积养老保住宅公积小计儿女教奉养老住宅贷款住宅租养老保险住宅公积金金险金育人利息金1,920.00416.00480.002,080.002,400.004,480.002,000.002,000.004,000.00-1,920.00416.00480.002,080.002,400.004,480.002,000.002,000.004,000.00-1,920.00416.00480.002,080.002,400.004,480.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-1,920.00416.00480.002,496.002,400.004,896.002,000.002,000.004,000.00-附带扣除额本月发生今年累计持续教儿女教奉养老住宅贷款住宅租持续教儿女教奉养老住宅贷住宅租持续教育育人利息金育育人款利息金育1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00 1,600.00500.00500.001,000.00400.002,500.002,500.005,000.00-2,000.00累计减除花费税款缴纳应纳税所得额税率速算扣除应钠税数额今年先期累今年应钠本期缴纳小计减除花费月计已交税款税款税款12,000.005,000.006.00-3%-----12,000.005,000.006.00-3%-----12,000.005,000.006.001,020.003%-30.60-30.6030.60 12,000.005,000.006.006,104.003%-183.12 2.52183.12180.60 12,000.005,000.006.0011,604.003%-348.1217.52348.12330.60 12,000.005,000.006.0017,104.003%-513.1232.52513.12480.60 12,000.005,000.006.0022,604.003%-678.1247.52678.12630.60 12,000.005,000.006.0028,104.003%-843.1262.52843.12780.60 12,000.005,000.006.0033,604.003%-1,008.1277.521,008.12930.60 12,000.005,000.006.0039,104.0010%2,520.001,390.4092.521,390.401,297.88 12,000.005,000.006.0042,104.0010%2,520.001,690.40107.521,690.401,582.88 12,000.005,000.006.0047,604.0010%2,520.002,240.402,642.522,240.40-402.12 12,000.005,000.006.0053,104.0010%2,520.002,790.402,657.522,790.40132.88 12,000.005,000.006.0058,604.0010%2,520.003,340.405,192.523,340.40-1,852.12 12,000.005,000.006.0064,104.0010%2,520.003,890.405,207.523,890.40-1,317.12 12,000.005,000.006.0069,604.0010%2,520.004,440.405,222.524,440.40-782.12 12,000.005,000.006.0075,104.0010%2,520.004,990.405,237.524,990.40-247.12 12,000.005,000.006.0080,604.0010%2,520.005,540.407,772.525,540.40-2,232.12 12,000.005,000.006.0086,104.0010%2,520.006,090.407,787.526,090.40-1,697.12 12,000.005,000.006.0091,604.0010%2,520.006,640.407,802.526,640.40-1,162.12 12,000.005,000.006.0097,104.0010%2,520.007,190.407,817.527,190.40-627.12 12,000.005,000.006.00102,604.0010%2,520.007,740.407,832.527,740.40-92.12 12,000.005,000.006.00108,104.0010%2,520.008,290.407,847.528,290.40442.88 12,000.005,000.006.00113,604.0010%2,520.008,840.407,862.528,840.40977.88 12,000.005,000.006.00119,104.0010%2,520.009,390.407,877.529,390.401,512.88 12,000.005,000.006.00124,604.0010%2,520.009,940.407,892.529,940.402,047.88 12,000.005,000.006.00130,104.0010%2,520.0010,490.407,907.5210,490.402,582.88 12,000.005,000.006.00135,604.0010%2,520.0011,040.4010,442.5211,040.40597.88 12,000.005,000.006.00141,104.0010%2,520.0011,590.4010,457.5211,590.401,132.88 12,000.005,000.006.00146,604.0020%16,920.0012,400.8010,472.5212,400.801,928.28 12,000.005,000.006.00152,104.0020%16,920.0013,500.8010,487.5213,500.803,013.28本月实发工应补（退）税资款-5,604.00-6,604.0030.607,573.40180.608,423.40330.609,273.40480.6010,123.40630.6010,973.40780.6011,823.40930.6012,673.401,297.8813,306.121,582.8813,521.12-402.1216,506.12132.8816,971.12 -1,852.1219,956.12-1,317.1220,421.12-782.1220,886.12-247.1221,351.12-2,232.1224,336.12-1,697.1224,801.12-1,162.1225,266.12-627.1225,731.12-92.1226,196.12442.8826,661.12977.8827,126.121,512.8827,591.122,047.8828,056.122,582.8828,521.12597.8831,506.121,132.8831,971.121,928.2832,175.723,013.2832,090.72。
