Need Salary Calculation from Net Salary to Gross Calcuation

[gsuresh123]

Hi,
I'm looking for salary calculator which calculate Gross Salary if we input Net Salary. The Components bifurcation is as follows:
Basic Salary: 70% of Gross Salary
HRA: 30% of Basic Salary

ESIC: Gross Salary*0.25%(if Gross salary is Greater than 21,000, ESIC=0)
EPF:Basic Salary*12%(If Basic Salary is greater than 15,000, EPF=0)
PT:If Gross is Less than 15000 than PT =0, If Gross is Greater than 15,000 and Less Than 20,000 then PT=150 , If Gross is greater than 20,000, PT=200

Based on the above criteria, please help me in calculating Gross Salary if I Input Net Salary in Excel.

Thanks & Regards
Suresh
9959172919

 

[Dave Patton]

See Excel's Goal Seek.

If you need further help, please post an example of your complete calculations.
You can use the Add-in XL2BB to create the example that is acceptable to this forum.

 

[Dave Patton]

Book1
	A	B	C	D	E	F	G
1	Gross	Basic Salary	HRA	ESIC	EPF	PT	Net
2	50,000.00	35,000.00	10,500.00	125.00	42.00	200.00	39,133.00
3	16,645.57	11,651.90	3,495.57	0.00	0.00	150.00	13,000.00
1d

Cell Formulas
Range	Formula
B2:B3	B2	=A2*0.7
C2:C3	C2	=B2*0.3
D2:D3	D2	=(A2>21000)*A2*0.25%
E2:E3	E2	=(B2>15000)*B2*0.12%
F2:F3	F2	=(A2>15000)*150+(A2>20000)*50
G2:G3	G2	=A2-SUM(C2:F2)

 

[gsuresh123]

See Excel's Goal Seek.

If you need further help, please post an example of your complete calculations.
You can use the Add-in XL2BB to create the example that is acceptable to this forum.

Hi,

Please find the calculation process. This involves in calculating Net Salary by Inputting Gross Salary, where as I need the reverse calculation , If I Input Net Salary, Gross Salary should calculate according to the conditions.

Component Category	Per month	Per Annum
Enter Gross Here	15000​
Basic Pay	10500​	126000​
HRA	4500​	54000​
Total Gross	15000​	180000​
Employee Contribution to PF	1260​	15120​
Employee Contribution to ESI	113​	1356​
Profession Tax	0​	0​
Net Take Home	13627​	163524​
Employer Contribution to PF	1260​	15120​
PF Admin charges	106​
Employer Contribution to ESI	488​	5856​
Cost to Company	16748​	200976​

 

[Dave Patton]

Did you try using Excel's Goal Seek?
See post #3 for an example.
Row 3 initially could be the same as Row 2.
Then use Goal Seek
Set Cell G3
To Value say 13000
By changing cell A3.
The result will be as shown; save the calculation.
Try the example and then try with your data.

If you use the Add-in XL2BB, the cell references and formulas will show in the post

 

[gsuresh123]

Did you try using Excel's Goal Seek?
See post #3 for an example.
Row 3 initially could be the same as Row 2.
Then use Goal Seek
Set Cell G3
To Value say 13000
By changing cell A3.
The result will be as shown; save the calculation.
Try the example and then try with your data.

If you use the Add-in XL2BB, the cell references and formulas will show in the post

Dear Dave,

I have seen the Post#3 and used Goal Seek Calculation also. But I need a reverse calculation by inputting Net Salary Component so that Gross Salary can be calculated based on the conditions. This actually we are trying for our ERP Payroll Module. So please share me reverse calculation function so that it can be implemented.

Regards
Suresh

 

[Dave Patton]

"Net Salary Component so that Gross Salary can be calculated based on the conditions."
I believe that is what post #3 shows.
Did you try the example in post 3 and use Goal Seek to calculate a new Gross?

 

[gsuresh123]

"Net Salary Component so that Gross Salary can be calculated based on the conditions."
I believe that is what post #3 shows.
Did you try the example in post 3 and use Goal Seek to calculate a new Gross?

Dear Dave,

As you mentioned using Goal Seek we can do that, and even i used goal seek also. But what i need is without using any goal seek options if we enter the Net salary, then we must get Gross Salary as per the conditions. No using of goal seek or any others.

Regards
Suresh
9959172919

 

[Dave Patton]

Book1
	A	B	C	D	E	F	G
12		Tax amounts		0.21334			150,000.00
13
14	The calculations are based on the information provided in post #1.
15	For amounts greater than your thresholds for tax components,
16	you can use: (G12+200)/(1-0.21334)					190,933.82
2a

Cell Formulas
Range	Formula
D12	D12	=0.7*0.3+0.0025+0.7*0.0012+0
F16	F16	=(Net_Amount+200)/(1-0.21334)

You can determine appropriate ranges and rates for the lower levels of income.

 

[Dave Patton]

Book1
	A	B	C	D	E	F	G	H	I
1	Gross	HRA	ESIC	EPF	PT	Net	Desired Net	Gross from net after tax
2	100.00	21.00	0.00	0.00	0.00	79.00	10,000.00
3	100.00	21.00	0.00	0.00	0.00	79.00	30,000.00		38,390.16
4	100.00	21.00	0.00	0.00	0.00	79.00	50,000.00		63,814.10
5	100.00	21.00	0.00	0.00	0.00	79.00	70,000.00		89,238.04
6	100.00	21.00	0.00	0.00	0.00	79.00	90,000.00		114,661.99
7	100.00	21.00	0.00	0.00	0.00	79.00	110,000.00		140,085.93
8	100.00	21.00	0.00	0.00	0.00	79.00	130,000.00		165,509.88
9	100.00	21.00	0.00	0.00	0.00	79.00	150,000.00		190,933.82
10
11	Initial information
2a

Cell Formulas
Range	Formula
B2:B9	B2	=A2*0.7*0.3
C2:C9	C2	=(A2>21000)*A2*0.25%
D2:D9	D2	=((A2*0.7)>15000)*A2*0.7*0.12%
E2:E9	E2	=(A2>15000)*150+(A2>20000)*50
F2:F9	F2	=A2-SUM(B2:E2)