如何通过税后实得工资,倒推出应发工资,即扣税运算的反运算?

Question

如何通过税后实得工资,倒推出应发工资,即扣税运算的反运算?
------------------------------
正向运算很好算,根据扣税换算表,正向推算.
但是我的需求是已知税后的实发工资,反推出应该给他多少钱,扣去税后正好等于提前定好的实发工资数.
如何实现呢?先发一个扣税换算表:级别   扣税比例                 下限                   上限                   乘项                   减项                   
---- -------------------- -------------------- -------------------- -------------------- -------------------- 
1    5%                   .00                  500.00               .05                  .00
2    10%-25               500.00               2000.00              .10                  25.00
3    15%-125              2000.00              5000.00              .15                  125.00
4    20%-375              5000.00              20000.00             .20                  375.00
5    25%-1375             20000.00             40000.00             .25                  1375.00
6    30%-3375             40000.00             60000.00             .30                  3375.00
7    35%-6375             60000.00             80000.00             .35                  6375.00
8    40%-10375            80000.00             100000.00            .40                  10375.00
9    45%-15375            100000.00            100000000.00         .45                  15375.00（所影响的行数为 9 行）
举例:
正常推算过程如下:(为了简化,不管保险的扣除问题,只论扣税,因为每月的三险一金是固定数)
员工税前实发工资为:4876
因为起征点为:2000
所以应纳税所得为:2876
通过上表可知,他的扣税比例为15%-125,
即 2876*10%-125 = 306.4
即应扣税为:306.4
所以,实发工资为:4876-306.4 = 4569.6我现在想实现,我想发给他4569.6,我给他的应发工资数(即扣税前的金额)是多少,如何算出来呢?

Answer 1

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-06-24 07:31:28
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (级别 INT,扣税比例 VARCHAR(9),下限 NUMERIC(8,2),上限 NUMERIC(11,2),乘项 NUMERIC(2,2),减项 NUMERIC(7,2))
INSERT INTO [tb]
SELECT 1,'5%',.00,500.00,.05,.00 UNION ALL
SELECT 2,'10%-25',500.00,2000.00,.10,25.00 UNION ALL
SELECT 3,'15%-125',2000.00,5000.00,.15,125.00 UNION ALL
SELECT 4,'20%-375',5000.00,20000.00,.20,375.00 UNION ALL
SELECT 5,'25%-1375',20000.00,40000.00,.25,1375.00 UNION ALL
SELECT 6,'30%-3375',40000.00,60000.00,.30,3375.00 UNION ALL
SELECT 7,'35%-6375',60000.00,80000.00,.35,6375.00 UNION ALL
SELECT 8,'40%-10375',80000.00,100000.00,.40,10375.00 UNION ALL
SELECT 9,'45%-15375',100000.00,100000000.00,.45,15375.00select * from [tb]with wang as
(
select *,实际工资下限=下限-减项,实际工资上限=上限-(上限-下限)*乘项-减项
from tb )select * ,实际工资=(4319.6+减项-下限*乘项)/(1-乘项) from wang
where 4319.6 between 实际工资下限 and 实际工资上限级别 扣税比例 下限 上限 乘项 减项 实际工资下限 实际工资上限 实际工资
3 0.15 2000.00 5000.00 0.15 125.00 1875.00 4425.0000 4876.000000000

Answer 2

实际工资=(4319.6+减项-下限*乘项)/(1-乘项)
是由计算公式倒推出来的。实际工资=x-(x-下限)*乘项-减项，然后求x=（实际工资+减项-下限*乘项)/(1-乘项)

Answer 3

--扣税表
create table tab(级别 int,扣税比例 varchar(20),下限 decimal(18,2),上限 decimal(18,2),乘项 decimal(18,2),减项 decimal(18,2))
insert tab
select 1,'5%',0.00,500.00,0.05,0.00 union all
select 2,'10%-25',500.00,2000.00,0.10,25.00 union all
select 3,'15%-125',2000.00,5000.00,0.15,125.00 union all
select 4,'20%-375',5000.00,20000.00 ,0.20 ,375.00 union all
select 5,'25%-1375',20000.00,40000.00,0.25,1375.00 union all
select 6,'30%-3375',40000.00,60000.00,0.30,3375.00 union all
select 7,'35%-6375',60000.00,80000.00,0.35,6375.00 union all
select 8,'40%-10375',80000.00,100000.00,0.40,10375.00 union all
select 9,'45%-15375',100000.00,100000000.00,0.45,15375.00select * from tab--实现根据工资计算所得工资
create table persion(编号 varchar(2),姓名 varchar(20),工资 decimal(18,2))
insert persion
select '01','五粮液',4876  union all
select '02','黄金酒',5343 union all
select '03','齐民思',8900 union all
select '04','二锅头',700 union all
select '05','琅琊台',300 union all
select '06','洛北春',15000
--实现根据工资计算所得工资
select T1.编号,T1.姓名,T1.工资-((T1.工资-T2.下限)*T2.乘项-T2.减项) 所得工资
from persion T1,tab T2
where T1.工资 Between T2.下限 AND T2.上限
--实现根据所得工资计算工资
--利用上述数据
select T1.编号,T1.姓名,T1.工资-((T1.工资-T2.下限)*T2.乘项-T2.减项) 所得工资
into persion1
from persion T1,tab T2
where T1.工资 Between T2.下限 AND T2.上限
select * from persion1
--根据标准表得出实际工资标准表
create table tab1(级别 int,上限  decimal(18,2),下限  decimal(18,2),实际下限 decimal(18,2),实际上限 decimal(18,2),乘项 decimal(18,2),减项 decimal(18,2))
insert tab1
select 级别,上限,下限,下限-减项,上限-((上限-下限)*乘项-减项),乘项,减项
from TAB
select * from tab1--得到工资
select T1.编号,T1.姓名,(T1.所得工资-减项-下限*乘项)/(1-乘项) 工资
from persion1 T1,tab1 T2
where T1.所得工资 Between T2.实际下限 AND T2.实际上限
--删除测试环境
drop table tab,persion,persion1,tab1

Answer 4

网上查到一个excel表公式
=ROUND(MAX((A2-B2-{0,0,25,125,375,1375,3375,6375,10375,15375})/(1-{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}))+B2,2)a2:实发税后工资
b2:起征点
结果：应发的税前工资思路：
按各种扣税比例依次测算，求出最大的的值，就是应发的税前工资，具体为什么，不明白比如：实发税后为3037,依次计算如下：5%:
(3037-2000)/0.95+2000 = 3091.5810%-25:
(3037-2000-25)/0.9+2000 = 3124.4415%-125:
(3037-2000-125)/0.85+2000 = 3072.9...
可知 3124.44 是最大值，
以其为税前工资正向推算
应纳税所得为1124.44,扣税比例为10%-125,
1124.44*0.1-25 = 87.44
3124.44-87.44 = 3037
可见推算是正确的。

Answer 5

to lz：
三楼的难道不是反向算的？实际工资 4319.6
得到原始工资：4876
难道不是你要的？先看清再说

Answer 6

IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] ([级别] INT,[扣税比例] VARCHAR(9),[下限] NUMERIC(8,2),[上限] NUMERIC(11,2),[乘项] NUMERIC(2,2),[减项] NUMERIC(7,2))
INSERT INTO [tb]
SELECT 1,'5%',0.00,500.00,.05,.00 UNION ALL
SELECT 2,'10%-25',500.00,2000.00,.10,25.00 UNION ALL
SELECT 3,'15%-125',2000.00,5000.00,.15,125.00 UNION ALL
SELECT 4,'20%-375',5000.00,20000.00,.20,375.00 UNION ALL
SELECT 5,'25%-1375',20000.00,40000.00,.25,1375.00 UNION ALL
SELECT 6,'30%-3375',40000.00,60000.00,.30,3375.00 UNION ALL
SELECT 7,'35%-6375',60000.00,80000.00,.35,6375.00 UNION ALL
SELECT 8,'40%-10375',80000.00,100000.00,.40,10375.00 UNION ALL
SELECT 9,'45%-15375',100000.00,100000000.00,.45,15375.00
--已知税前工资求应纳税额和税后工资
;with t as
(
select *,[税前工资下限]=2000+[下限],[税前工资上限]=2000+[上限],[税后工资下限]=2000+[下限]-([下限]*[乘项]-[减项]),[税后工资上限]=2000+[上限]-([上限]*[乘项]-[减项])
from tb 
)
select [应纳税额]=(4876-2000)* [乘项]-[减项],[税后工资]=4876* (1-[乘项])+2000* [乘项]+[减项]
 from t
where 4876 between [税前工资下限] and [税前工资上限]
/*
应纳税额 税后工资
306.40  4569.60
*/
--已知税后工资求税前工资
;with t as
(
select *,[税前工资下限]=2000+[下限],[税前工资上限]=2000+[上限],[税后工资下限]=2000+[下限]-([下限]*[乘项]-[减项]),[税后工资上限]=2000+[上限]-([上限]*[乘项]-[减项])
from tb 
)
select [税前工资]=cast((4569.6-[减项]-2000* [乘项])/(1-[乘项]) as float) from t
where 4569.6 between [税后工资下限] and [税后工资上限]
/*
税前工资
4876
*/

Answer 7

--扣税表 
create table tab(级别 int,扣税比例 varchar(20),下限 decimal(18,2),上限 decimal(18,2),乘项 decimal(18,2),减项 decimal(18,2)) 
insert tab 
select 1,'5%',0.00,500.00,0.05,0.00 union all 
select 2,'10%-25',500.00,2000.00,0.10,25.00 union all 
select 3,'15%-125',2000.00,5000.00,0.15,125.00 union all 
select 4,'20%-375',5000.00,20000.00 ,0.20 ,375.00 union all 
select 5,'25%-1375',20000.00,40000.00,0.25,1375.00 union all 
select 6,'30%-3375',40000.00,60000.00,0.30,3375.00 union all 
select 7,'35%-6375',60000.00,80000.00,0.35,6375.00 union all 
select 8,'40%-10375',80000.00,100000.00,0.40,10375.00 union all 
select 9,'45%-15375',100000.00,100000000.00,0.45,15375.00 
select * from tab --实现根据工资计算所得工资 
create table persion(编号 varchar(2),姓名 varchar(20),工资 decimal(18,2)) 
insert persion 
select '01','五粮液',4876 union all 
select '02','黄金酒',5343 union all 
select '03','齐民思',8900 union all 
select '04','二锅头',700 union all 
select '05','琅琊台',300 union all 
select '06','洛北春',15000 
--实现根据工资计算所得工资 
select T1.编号,T1.姓名,T1.工资-(T1.工资-T2.下限)*T2.乘项-T2.减项 所得工资 
from persion T1,tab T2 where T1.工资 Between T2.下限 AND T2.上限 --实现根据所得工资计算工资 
--利用上述数据 
select T1.编号,T1.姓名,T1.工资-(T1.工资-T2.下限)*T2.乘项-T2.减项 所得工资 
into persion1 from persion T1,tab T2 
where T1.工资 Between T2.下限 AND T2.上限 
select * from persion1 
--根据标准表得出实际工资标准表 
create table tab1(级别 int,上限 decimal(18,2),下限 decimal(18,2),实际下限 decimal(18,2),实际上限 decimal(18,2),乘项 decimal(18,2),减项 decimal(18,2)) 
insert tab1 
select 级别,上限,下限,下限-减项,上限-(上限-下限)*乘项-减项,乘项,减项 
from TAB select * from tab1 
--得到工资 
select T1.编号,T1.姓名,(T1.所得工资+减项-下限*乘项)/(1-乘项) 工资 
from persion1 T1,tab1 T2 
where T1.所得工资 Between T2.实际下限 AND T2.实际上限 
--删除测试环境 
drop table tab,persion,persion1,tab1

Answer 8

晕这不是 x,y的二项式么？fn(x)=y , 已知x, 求y ，很好求吧
倒推过来，已知y，难道不会求x了？

Answer 9

--创建扣税换算表
CREATE TABLE [tb] (
[下限] [numeric](18, 2),
[上限] [numeric](18, 2),
[乘项] [numeric](18, 2) ,
[减项] [numeric](18, 2) ,
[起征点] [numeric](18, 2) ,
[税前工资下限] [numeric](18, 2),
[税前工资上限] [numeric](18, 2) ,
[税后工资下限] [numeric](18, 2) ,
[税后工资上限] [numeric](18, 2) )
insert into tb select 0.00,500.00,0.05,0.00,2000.00,2000.00,2500.00,2000.00,2475.00
insert into tb select 500.00,2000.00,0.10,25.00,2000.00,2500.00,4000.00,2475.00,3825.00
insert into tb select 2000.00,5000.00,0.15,125.00,2000.00,4000.00,7000.00,3825.00,6375.00
insert into tb select 5000.00,20000.00,0.20,375.00,2000.00,7000.00,22000.00,6375.00,18375.00
insert into tb select 20000.00,40000.00,0.25,1375.00,2000.00,22000.00,42000.00,18375.00,33375.00
insert into tb select 40000.00,60000.00,0.30,3375.00,2000.00,42000.00,62000.00,33375.00,47375.00
insert into tb select 60000.00,80000.00,0.35,6375.00,2000.00,62000.00,82000.00,47375.00,60375.00
insert into tb select 80000.00,100000.00,0.40,10375.00,2000.00,82000.00,102000.00,60375.00,72375.00
insert into tb select 100000.00,100000000.00,0.45,15375.00,2000.00,102000.00,100002000.00,72375.00,55017375.00select *
from tb/*下限                   上限                   乘项                   减项                   起征点                  税前工资下限               税前工资上限               税后工资下限               税后工资上限               
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
.00                  500.00               .05                  .00                  2000.00              2000.00              2500.00              2000.00              2475.00
500.00               2000.00              .10                  25.00                2000.00              2500.00              4000.00              2475.00              3825.00
2000.00              5000.00              .15                  125.00               2000.00              4000.00              7000.00              3825.00              6375.00
5000.00              20000.00             .20                  375.00               2000.00              7000.00              22000.00             6375.00              18375.00
20000.00             40000.00             .25                  1375.00              2000.00              22000.00             42000.00             18375.00             33375.00
40000.00             60000.00             .30                  3375.00              2000.00              42000.00             62000.00             33375.00             47375.00
60000.00             80000.00             .35                  6375.00              2000.00              62000.00             82000.00             47375.00             60375.00
80000.00             100000.00            .40                  10375.00             2000.00              82000.00             102000.00            60375.00             72375.00
100000.00            100000000.00         .45                  15375.00             2000.00              102000.00            100002000.00         72375.00             55017375.00（所影响的行数为 9 行）
*/
--创建测试数据
create table test (编号 varchar(5),姓名 varchar(10),税前 numeric(18,2),税后 numeric(18,2))insert into test select '001','fdg',0,6569.60
insert into test select '003','sfgs',0,5708.00
insert into test select '009','wert',0,4156.27
insert into test select '012','fdgs',0,4158.65
insert into test select '036','xvcb',0,5708.00
insert into test select '064','df',0,4161.02
insert into test select '094','qw',0,4076.00
insert into test select '097','fv',0,4026.60
insert into test select '119','df',0,4131.12
insert into test select '124','yty',0,4065.25
insert into test select '131','vb',0,4076.00
insert into test select '143','cd',0,5950.04
insert into test select '147','rt',0,4032.30
insert into test select '151','wds',0,6274.03
insert into test select '157','gb',0,5830.56
insert into test select '160','fgr',0,4057.85
insert into test select '168','rt',0,4552.94
insert into test select '169','ere',0,4970.88
select *
from test/*
--以下是税前没有值的状态
编号    姓名         税前                   税后                   
----- ---------- -------------------- -------------------- 
001   fdg        .00                  6569.60
003   sfgs       .00                  5708.00
009   wert       .00                  4156.27
012   fdgs       .00                  4158.65
036   xvcb       .00                  5708.00
064   df         .00                  4161.02
094   qw         .00                  4076.00
097   fv         .00                  4026.60
119   df         .00                  4131.12
124   yty        .00                  4065.25
131   vb         .00                  4076.00
143   cd         .00                  5950.04
147   rt         .00                  4032.30
151   wds        .00                  6274.03
157   gb         .00                  5830.56
160   fgr        .00                  4057.85
168   rt         .00                  4552.94
169   ere        .00                  4970.88
*/--进行计算
update test
set 税前 = (税后-b.起征点*b.乘项-b.减项)/(1-b.乘项)
from test a left join tb b on (a.税后 >b.税后工资下限 and a.税后<=b.税后工资上限)/*
--这是计算后的结果
编号    姓名         税前                   税后                   
----- ---------- -------------------- -------------------- 
001   fdg        7243.25              6569.60
003   sfgs       6215.29              5708.00
009   wert       4389.73              4156.27
012   fdgs       4392.53              4158.65
036   xvcb       6215.29              5708.00
064   df         4395.32              4161.02
094   qw         4295.29              4076.00
097   fv         4237.18              4026.60
119   df         4360.14              4131.12
124   yty        4282.65              4065.25
131   vb         4295.29              4076.00
143   cd         6500.05              5950.04
147   rt         4243.88              4032.30
151   wds        6881.21              6274.03
157   gb         6359.48              5830.56
160   fgr        4273.94              4057.85
168   rt         4856.40              4552.94
169   ere        5348.09              4970.88（所影响的行数为 18 行）
*/

Answer 10

with wang as
(
select *,实际工资下限=下限-减项,实际工资上限=上限-(上限-下限)*乘项+减项
from tb )select * ,税前=(4569.6-减项-下限*乘项)/(1-乘项) from wang
where 4569.6 between 实际工资下限 and 实际工资上限级别 扣税比例 下限 上限 乘项 减项 实际工资下限 实际工资上限 税前
3 15%-125 2000.00 5000.00 0.15 125.00 1875.00 4675.0000 4876.000000000