Find Gross wage from net pay

[Vanda_a]

Hello all.

Could anyone teach me how to find gross salary if I have a net pay of 12,204,000?
The gross is 13,742,520 but I don't know how to build a auto calculate table in excel[TABLE="width: 123"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
ex: 13,742,520 goes range 20%. so

13,742,520 x 20% = 2,748,504 - 1,210,000 = 1,538,504 tax.
13,742,520 - 1,538,504 = 12,204,016[TABLE="width: 123"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
​

<strike></strike>
below is table range of wage.
[TABLE="width: 380"]
<tbody>[TR]
[TD="align: left"]from[/TD]
[TD="align: left"]to[/TD]
[TD="align: left"]rate[/TD]
[TD="align: left"]allowance[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1,200,000[/TD]
[TD]0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1,200,001[/TD]
[TD]2,000,000[/TD]
[TD]5%[/TD]
[TD]-60,000[/TD]
[/TR]
[TR]
[TD]2,000,001[/TD]
[TD]8,500,000[/TD]
[TD]10%[/TD]
[TD]-160,000[/TD]
[/TR]
[TR]
[TD]8,500,001[/TD]
[TD]12,500,000[/TD]
[TD]15%[/TD]
[TD]-585,000[/TD]
[/TR]
[TR]
[TD]12,500,001[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]-1,210,000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 123"]
<tbody>[TR]
[TD]Thank you very much.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

I am not sure of your question.

I used goal seek to calculate c3.

Excel 2010
	A	B	C	D	E	F	G
1	Gross	Tax	Net
2	13,742,520.00	1,538,504.00	12,204,016.00		Bracket	Rate	rDiff
3	11,076,470.41	1,076,470.56	9,999,999.85		0	0%	0%
4	12,500,000.00	1,290,000.00	11,210,000.00		1,200,000	5%	5%
5					2,000,000	10%	5%
6					8,500,000	15%	5%
7					12,500,000	20%	5%
8
4d

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3	=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)
B4	=SUMPRODUCT(--(A4>rB),A4-rB,rDiff)
C2	=A2-B2
C3	=A3-B3
C4	=A4-B4
G3	=F3-N(F2)

Named Ranges
Name	Refers To	Cells
rB	='4d'!$E$3:$E$7
rDiff	='4d'!$G$3:$G$7

 

[Dave Patton]

I am not sure of your question.

I used goal seek to calculate c3.

Excel 2010
	A	B	C	D	E	F	G
1	Gross	Tax	Net
2	13,742,520.00	1,538,504.00	12,204,016.00		Bracket	Rate	rDiff
3	11,076,470.59	1,076,470.59	10,000,000.00		0	0%	0%
4	12,500,000.00	1,290,000.00	11,210,000.00		1,200,000	5%	5%
5					2,000,000	10%	5%
6					8,500,000	15%	5%
7					12,500,000	20%	5%
8
4d

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3	=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)
B4	=SUMPRODUCT(--(A4>rB),A4-rB,rDiff)
C2	=A2-B2
C3	=A3-B3
C4	=A4-B4
G3	=F3-N(F2)

Named Ranges
Name	Refers To	Cells
rB	='4d'!$E$3:$E$7
rDiff	='4d'!$G$3:$G$7

 

[Vanda_a]

Good day Dave.

May you help find gross base on net pay? above formula is base on gross and calculate net pay.
My issue is, i have only a net 12,204,016 and i don't know what formula to compute in order to have a gross 13,742,520.00

Thank you very much

 

[Michael M]

Divide your nett by (100%-20%)or 80%
thenn times the result by 100

 

[Dave Patton]

In the initial post, I mentioned Goal Seek.

See the example below.

The initial information is in A2:C2

Select C2
Access Goal Seek see Data / What-if Analysis / Goal Seek

In Goal Seek's To Value enter 12204016
then "By Changing " enter A2
Press OK

Excel will calculate the value.
The results will look like A3:C3

Excel 2010
	A	B	C	D	E	F	G
1	Gross	Tax	Net
2	10,000,000.00	915,000.00	9,085,000.00		Bracket	Rate	rDiff
3	13,742,520.00	1,538,504.00	12,204,016.00		0	0%	0%
4					1,200,000	5%	5%
5					2,000,000	10%	5%
6					8,500,000	15%	5%
7					12,500,000	20%	5%
8
4d

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rDiff)
B3	=SUMPRODUCT(--(A3>rB),A3-rB,rDiff)
C2	=A2-B2
C3	=A3-B3
G3	=F3-N(F2)

Named Ranges
Name	Refers To	Cells
rB	='4d'!$E$3:$E$7
rDiff	='4d'!$G$3:$G$7

 

[Vanda_a]

Hello again.

I think i confused you. My goal seek is A2?
This amount 12,204,016.00 i already have it in my data and i am seeking 13,742,520.00 (A2).

Thank you

 

[Vanda_a]

Divide your nett by (100%-20%)or 80%
thenn times the result by 100

Hello. Not correct. I would like to have a table as Dave but seeking for gross, not net. I have net already and dont know how to find gross

Thank you

 

[Dave Patton]

Please try the suggestion in Post # 6.

The idea is to use Goal Seek to calculate A2 the Gross by entering the known net in the Goal Seek.

 

[joeu2004]

I think i confused you. My goal seek is A2? This amount 12,204,016.00 i already have it in my data and i am seeking 13,742,520.00 (A2).

What Dave is trying to explain is: ostensibly, we must "seek" the gross amount by trial-and-error, because of the recursive nature of the algebraic derivation, to wit:

G = (N+A) / (1-r)

where G=gross, N=net, A=allowance (negative), and r=tax rate. The problem is: A and r depend on G, which we are trying to derive.

However, I have found a way to derive G directly with a formula.

Book1
	A	B	C	D	E
1
2	target net	12,204,016.00
3	gross	13,742,520.00
4	tax	1,538,504.00
5	net	12,204,016.00		net = target net?	TRUE
6
7	gross over...	to	rate	allowance	net over...
8	0.00	1,200,000.00	0%	0	0.00
9	1,200,000.00	2,000,000.00	5%	-60,000.00	1,200,000.00
10	2,000,000.00	8,500,000.00	10%	-160,000.00	1,960,000.00
11	8,500,000.00	12,500,000.00	15%	-585,000.00	7,810,000.00
12	12,500,000.00		20%	-1,210,000.00	11,210,000.00
Sheet1

Formulas:
B3: =ROUND((B2 + INDEX($D$8:$D$12, SUMPRODUCT(--(B2>$E$8:$E$12)))) / (1 - INDEX($C$8:$C$12, SUMPRODUCT(--(B2>$E$8:$E$12)))), 0)
B4: =ROUND(B3*VLOOKUP(B3,$A$8:$D$12,3) + VLOOKUP(B3,$A$8:$D$12,4), 0)
B5: =B3 - B4
E5: =B5=B2
E8: =A8*(1 - C8) - D8
Copy E8 into E9:E12

Change ROUND(...,0) to ROUND(...,2) in B3 and B4 if you want the gross, tax and net amounts to be accurate to the 1/100 unit currency ("cent").

The SUMPRODUCT expression determines the highest table index for the target net. The INDEX expressions select the corresponding tax rate (column C) and allowance (column D). The VLOOKUP expressions match the same corresponding tax rate and allowance, given the gross amount.

Note, again, that you entered the allowance as negative numbers. That is why we use "+ VLOOKUP(...,4)" to subtract the allowance in B4.