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]

 

[Vanda_a]

Your English is good. I think we just disagree about methodology.

Hello again. Could you help to explain what the Max does in the formula please?

Thank you very much

 

[joeu2004]

Could you help to explain what the Max does in the formula please?

MAX is used in two formulas, namely:

B5:  { =MAX((B2 - B3*$C$15:$C$19 - $D$15:$D$19) / (1 - $C$15:$C$19)) }
B7:  =MAX(0, B5+B6)

In B7, MAX ensures that when the calculated gross is less than the deduction, the taxable salary is zero instead of negative.

Presumably, you are interested in the use of MAX in B5. To understand, change the target net to 7,000,000, and temporarily enter the following formula into F15 and copy down:

=($B$2 - $B$3*C15 - D15) / (1 - C15)

This is demonstrated in the following table:

Book1
	A	B	C	D	E	F
2	target net	7,000,000.00
3	deductions	123,456.00
4
5	gross	7,586,282.67
6	deductions	-123,456.00
7	taxable salary	7,462,826.67
8	tax	586,282.67
9
10	gross	7,586,282.67
11	tax	-586,282.67
12	net	7,000,000.00
13
14	taxable salary over...	to...	rate	allowance	diff rate	gross= max of...
15	0.00	1,200,000.00	0.00%	0.00	0.00%	7,000,000.00
16	1,200,000.00	2,000,000.00	5.00%	60,000.00	5.00%	7,298,765.47
17	2,000,000.00	8,500,000.00	10.00%	160,000.00	5.00%	7,586,282.67
18	8,500,000.00	12,500,000.00	15.00%	585,000.00	5.00%	7,525,272.47
19	12,500,000.00		20.00%	1,210,000.00	5.00%	7,206,636.00
Sheet1

If we use each of the calculated gross amounts (F15:F19) in the formulas for tax and net, all of them derive the target net amount.

As we can see, the best gross amount is in F17. That is, it is the maximum of all of the possible calculated gross amounts.

And that is what we calculate in B5 without the need for the "helper" cells in F15:F19.

-----

The problem is with the recursive calculation of the gross amount. The algebraic derivation is shown belown (all amounts are positive values).

gross - tax = net

gross - ( (gross - deductions)*rate - allowance ) = net

gross*(1 - rate) = net - deductions*rate - allowance

gross = (net - deductions*rate - allowance) / (1 - rate)

Because rate is used in both the numerator and the dividend, we cannot determine the appropriate rate by looking up the numerator, as I effectively did before we included deductions in the equation (posts #10 and 11).

So we simply calculate the gross for all rates, then choose the best (maximum) calculated gross amount.

 

[joeu2004]

If we use each of the calculated gross amounts (F15:F19) in the formulas for tax and net, all of them derive the target net amount.

Well, that probably didn't make good sense to you.

What I meant was: if we substitute each calculated gross amount into your formulas for tax and net
-- namely, tax = (gross - deductions)*rate - allowance and net = gross - tax --
all of them derive the target net amount. See column G in the table below.

But that is tautological, since we used those formulas to derive the gross amount in column F. (Klunk!)

However, only the max gross amount derives the target net amount when we use the actual tax based on max gross minus deductions (taxable salary).

I know that is correct. It is demonstrated in columns H:J in the table below. And it made some intuitive sense to me when I first derived the solution 8 days ago.

But frankly, I'm at a loss to explain it now. I'm distracted by other things to do. Sorry.

Book1
	A	B	C	D	E	F	G	H	I	J
2	target net	7,000,000.00
3	deductions	123,456.00
4
5	gross	7,586,282.67
6	deductions	-123,456.00
7	taxable salary	7,462,826.67
8	tax	586,282.67
9
10	gross	7,586,282.67
11	tax	-586,282.67
12	net	7,000,000.00
13
14	taxable salary over...	to...	rate	allowance	diff rate	gross= max of...	net = g - (g-d)*r + a	taxl sal = g - d	actl tax on taxl sal	net = g - actlTax
15	0.00	1,200,000.00	0.00%	0.00	0.00%	7,000,000.00	7,000,000.00	6,876,544.00	527,654.40	6,472,345.60
16	1,200,000.00	2,000,000.00	5.00%	60,000.00	5.00%	7,298,765.47	7,000,000.00	7,175,309.47	557,530.95	6,741,234.53
17	2,000,000.00	8,500,000.00	10.00%	160,000.00	5.00%	7,586,282.67	7,000,000.00	7,462,826.67	586,282.67	7,000,000.00
18	8,500,000.00	12,500,000.00	15.00%	585,000.00	5.00%	7,525,272.47	7,000,000.00	7,401,816.47	580,181.65	6,945,090.82
19	12,500,000.00		20.00%	1,210,000.00	5.00%	7,206,636.00	7,000,000.00	7,083,180.00	548,318.00	6,658,318.00
Sheet1

G15: =F15 - (F15 - $B$3)*C15 + D15
H15: =F15 - $B$3
I15: =SUMPRODUCT((H15 > $A$15:$A$19)*(H15 - $A$15:$A$19), $E$15:$E$19)
J15: =F15 - I15
Copy G15:J15 into G16:J19

The algebraic derivation is shown belown (all amounts are positive values).
gross - tax = net
gross - ( (gross - deductions)*rate - allowance ) = net
gross*(1 - rate) = net - deductions*rate - allowance
gross = (net - deductions*rate - allowance) / (1 - rate)

That probably would be clearer if I add one step (in red):

gross - tax = net

gross - ( (gross - deductions)*rate - allowance ) = net

gross - gross*rate + deductions*rate + allowance = net

gross*(1 - rate) = net - deductions*rate - allowance

gross = (net - deductions*rate - allowance) / (1 - rate)

 

[Vanda_a]

Well, that probably didn't make good sense to you.

Good morning. Really thanks for your explanation. Really appreciate.. Thank you very much

 

[joeu2004]

You're welcome.