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]

Book1
	A	B	C	D	E	F	G
1	Gross	HRA	ESIC	EPF	PT	Net	Desired Net
2	12,658.23	2,658.23	0.00	0.00	0.00	10,000.00	10,000.00
3	38,390.16	8,061.93	95.98	32.25	200.00	30,000.00	30,000.00
4	63,814.10	13,400.96	159.54	53.60	200.00	50,000.00	50,000.00
5	89,238.04	18,739.99	223.10	74.96	200.00	70,000.00	70,000.00
6	114,661.99	24,079.02	286.65	96.32	200.00	90,000.00	90,000.00
7	140,085.93	29,418.05	350.21	117.67	200.00	110,000.00	110,000.00
8	165,509.88	34,757.07	413.77	139.03	200.00	130,000.00	130,000.00
9	190,933.82	40,096.10	477.33	160.38	200.00	150,000.00	150,000.00
10
11	A picture of the result after using Goal Seek or running the Sub GoalSeek
12
13
2aa

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)

 

[Dave Patton]

Sub GoalSeek()
Dim i As Long
For i = 2 To 9 'you can here define the start row number and to the last row you want
Range("F" & i).GoalSeek Goal:=Range("G" & i).Value, ChangingCell:=Range("A" & i)
Next
End Sub

 

[gsuresh123]

Book1
	A	B	C	D	E	F	G
1	Gross	HRA	ESIC	EPF	PT	Net	Desired Net
2	12,658.23	2,658.23	0.00	0.00	0.00	10,000.00	10,000.00
3	38,390.16	8,061.93	95.98	32.25	200.00	30,000.00	30,000.00
4	63,814.10	13,400.96	159.54	53.60	200.00	50,000.00	50,000.00
5	89,238.04	18,739.99	223.10	74.96	200.00	70,000.00	70,000.00
6	114,661.99	24,079.02	286.65	96.32	200.00	90,000.00	90,000.00
7	140,085.93	29,418.05	350.21	117.67	200.00	110,000.00	110,000.00
8	165,509.88	34,757.07	413.77	139.03	200.00	130,000.00	130,000.00
9	190,933.82	40,096.10	477.33	160.38	200.00	150,000.00	150,000.00
10
11	A picture of the result after using Goal Seek or running the Sub GoalSeek
12
13
2aa

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)

Dear Dave,

Thanks for your reply, But As I mentioned the calculation part which i require is inthe following way which was given by you is wrong.

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.

 

[gsuresh123]

Dear Dave,

Thanks for your reply, But As I mentioned the calculation part which i require in the following way which was given by you is wrong as there is no Basic Salary Calculation as well ESIC. PF and PT were not calculated for 10000 Salary. Requesting you to please send me correct sheet. If I give input for Net Salary, Gross Salary to be calculated automatically.



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.

 

[Dave Patton]

Please review the calculation that I provided.
If I misunderstood your information, please clarify.

The Basic Salary does not have to show on the sheet to complete the calculations.

Book1
	C	D	E	F	G	H	I
3	Gross	Basic Salary	HRA	ESIC	EPF	PT	Net
4	50,000.00	35,000.00	10,500.00	125.00	42.00	200.00	39,133.00
5	12,658.23	8,860.76	2,658.23	0.00	0.00	0.00	10,000.00
6
7
8
9	Gross	HRA	ESIC	EPF	PT	Net
10	50,000.00	10,500.00	125.00	42.00	200.00	39,133.00
11	12,658.23	2,658.23	0.00	0.00	0.00	10,000.00
3a

Cell Formulas
Range	Formula
D4:D5	D4	=C4*0.7
E4:E5	E4	=D4*0.3
F4:F5	F4	=(C4>21000)*C4*0.25%
G4:G5	G4	=(D4>15000)*D4*0.12%
H4:H5	H4	=(C4>15000)*150+(C4>20000)*50
I4:I5	I4	=C4-SUM(E4:H4)
D10:D11	D10	=C10*0.7*0.3
E10:E11	E10	=(C10>21000)*C10*0.25%
F10:F11	F10	=(C10*0.7>15000)*C10*0.7*0.12%
G10:G11	G10	=(C10>15000)*150+(C10>20000)*50
H10:H11	H10	=C10-SUM(D10:G10)

 

[Dave Patton]

Book1
	B	C	D	E	F	G	H	I
9		Gross	HRA	ESIC	EPF	PT	Net
10
11		12,658.23	2,658.23	0.00	0.00	0.00	10,000.00	12,658.23
12		12,658.23	2,658.23	0.00	0.00	0.00	10,000.00	12,658.23
13		12,658.23	2,658.23	0.00	0.00	0.00	10,000.00	12,658.23
14
15	In previous threads, I showed how the factor was calculated for the highest incomes.
16	In I11, I show how the factor was calculated for the lowest levels of income.
17	N.B. I12 and I13 are equivalent to I11.
18	In previous threads, I advised that you can calculate the factor for the various levels of tax.
19
3a

Cell Formulas
Range	Formula
D11:D13	D11	=C11*0.7*0.3
E11:E13	E11	=(C11>21000)*C11*0.25%
F11:F13	F11	=(C11*0.7>15000)*C11*0.7*0.12%
G11:G13	G11	=(C11>15000)*150+(C11>20000)*50
H11:H13	H11	=C11-SUM(D11:G11)
I11	I11	=H11/(1-(0.7*0.3))
I12	I12	=H12/0.79
I13	I13	=1.265823*H13

 

[Dave Patton]

Book1
	B	C	D	E	F	G	H
20		Net	Gross
21		10,000.00	12,658.23
22	Replace the "99999", with if fuctions for the subsequent levels of net income and rates.
23
3a

Cell Formulas
Range	Formula
D21	D21	=IF(C21<=11850,C21*1.265823,99999)

or calculate the factors and use a Lookup function.

 

[gsuresh123]

Book1
	B	C	D	E	F	G	H
20		Net	Gross
21		10,000.00	12,658.23
22	Replace the "99999", with if fuctions for the subsequent levels of net income and rates.
23
3a

Cell Formulas
Range	Formula
D21	D21	=IF(C21<=11850,C21*1.265823,99999)

or calculate the factors and use a Lookup function.

Dear Dave,

I Understood that you are doing every thing but missing one major part. Please review the below details once.

Component Category	Per month	Per Annum
Enter Gross Here	12658​
Basic Pay	8861​	106332​
HRA	3797​	45564​
Total Gross	12658​	151896​
Employee Contribution to PF	1063​	12756​
Employee Contribution to ESI	95​	1140​
Profession Tax	0​	0​
Net Take Home	11500​	138000​
Employer Contribution to PF	1063​	12756​
PF Admin charges	89​
Employer Contribution to ESI	411​	4932​
Cost to Company	14132​	169584​

As per the attached annexure, If i'm giving the Gross Salary , I'm getting Net Salary as per all the above calculations. but I require, I input the Net Salary, I Should get the Gross Salary based on the same calculations. It is reverse calculation process.

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

In the sheets which you have provided me the calculation part, Gross salary above 21429 there should be no calculation of PF and Gross Salary above 21000 there is no ESIC Deduction and only PT deduction will be there, but you have given calculations for all.

Requesting you to please review and let me know if you need any further inputs on the same.

Regards
Suresh
+91-9959172919

 

[Dave Patton]

You did not not advise what part of the calculation you are having a problem with.
This is your spreadsheet. You can edit the suggested formulas to meet your requirements.
You can edit the tax factor for the different tax levels and use nested If functions to apply the factor that you calculate.
If you need assistance, post an example with XL2BB and explain clearly your specific questions.

 

[gsuresh123]

Book1
	F	G	H
6	Component Category	Per month	Per Annum
7	Enter Gross Here	15000
8	Basic Pay	10500	126000
9	HRA	4500	54000
10	Total Gross	15000	180000
11	Employee Contribution to PF	1260	15120
12	Employee Contribution to ESI	113	1356
13	Profession Tax	0	0
14	Net Take Home	13627	163524
15	Employer Contribution to PF	1260	15120
16	PF Admin charges	106
17	Employer Contribution to ESI	488	5856
18	Cost to Company	16748	200976
Sheet2

Cell Formulas
Range	Formula
H8:H15, H17:H18	H8	=G8*12
G8	G8	=ROUND(G7*0.7,0)
G9	G9	=G7-G8
G10	G10	=SUM(G8:G9)
G11	G11	=ROUND(IF(G8>15001.01,0,IF(G8<15000.01,G8*0.12,0)),0)
G12	G12	=ROUND(IF(G10<21000.01,G10*0.75/100,IF(G10>21000.01,0)),0)
G13	G13	=ROUND(IF(G10<15000.01,0,IF(G10<20000.01,150,IF(G10>20000.01,200))),0)
G14	G14	=G10-G11-G12-G13
G15	G15	=ROUND(IF(G8>15001.01,0,IF(G8<15000.01,G8*12%,0)),0)
G16	G16	=ROUND(IF(G8>15001.01,0,IF(G8<15000.01,G8*1.01%,0)),0)
G17	G17	=ROUND(IF(G10<21000.01,G10*3.25/100,IF(G10>21000.01,0)),0)
G18	G18	=G10+G15+G17