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

<tbody>
</tbody>

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

<tbody>
</tbody>

​

<strike></strike>
below is table range of wage.

from	to	rate	allowance
0	1,200,000	0%
1,200,001	2,000,000	5%	-60,000
2,000,001	8,500,000	10%	-160,000
8,500,001	12,500,000	15%	-585,000
12,500,001		20%	-1,210,000

<tbody>
</tbody>

Thank you very much.

<tbody>
</tbody>

 

[joeu2004]

The net is not really that net. Example Post#18 . Gross 13,865,976 - tax 1,538,504 = 12,327,472 which is not equal the target net.
13,865,976 x 20% - 1,210,000 = 1,563,195.20. Gross 13,865,976 - 1,563,192.20 = 12,302,780.80 which is not the target net.

I think you misunderstand my calculations in post #18 .

The "generated gross" is still 13,742,520 in B5. The calculated tax is still 13,742,520*20% - 1,210,000 = 1,538,504 in B6. And the calculated net is still 13,742,520 - 1,538,504 = 12,204,016 in B7. And that matches the target net in B2, as demonstrated by the comparison in E7.

-----

In post #17 , you wrote: ``I would like to add whatever amount to the generated gross and would like to keep the net the same``.

I should have asked you to provide an example of the calculation.

And I should have asked what you call the "amount" to be added to the "generated gross", and what you call the result of adding that amount to the "generated gross".

I am asking you for that information now.

-----

I thought I understood. (Frankly, I still do.)

Lacking a concrete example, I made up my own "additional amount" (123,456 in in B4). And I add it to the "generated gross", creating 13,742,520 + 123,456 = 13,865,976 in B3.

I think you are confused because I also changed the names of things in order to conform to typical salary terminology (in the USA, at least).

I assumed that 13,865,976 is the contractual salary that the employee earns. In the US, we call that amount the "gross".

And I assumed that 123,456 is the "deductions" from gross. In the US, that is the amounts that are subtracted from the contractual salary before calculating tax on the remainder.

So I renamed the "generated gross" to "taxable income". In the US, that is what we call remaining amount that is taxed.

I believe the calculations in post #18 are correct, according to your specifications. Only the names have been changed.

The amount that I call "taxable income" is the amount that you call "generated gross".

And the amount that I call "gross" is the amount that results from "adding whatever amount to the generated gross, keeping the net the same", as you required.

-----

Be that as it made, all will be clearer if we continue to call the amount in B5 the "generated gross" the "gross".

And if you tell me what to call the amounts in B4 and B3: the amount added to the "generated gross" (B4); and the result of that addition (B3).

 

[Vanda_a]

I think you misunderstand my calculations in post #18 .

Good evening. I re checked it and it works perfectly. Really sorry for my miss calculation.

Thank you very much.

 

[Vanda_a]

Post#22 deleted.

I think you misunderstand my calculations in post #18 .

Example. I have a gross 13,865,976. But my taxable salary is just only

13,742,520 because I have child/spouse for deduction​

123,456.
And my tax salary is

1,538,504. Then my salary to be received is my gross minus tax = 12,327,472 which is not the target net. Hope this is clearer.
My goal is if a staff has a child for tax deduction 123,456 and she/he requests a net salary of

12,204,016. How much gross I have to declare to tax authority in order for her/him to get

12,204,016.

Thank you​

​

_{^{<strike>
</strike>}}​

​

 

[joeu2004]

Example. I have a gross 13,865,976. But my taxable salary is just only 13,742,520 because I have child/spouse for deduction 123,456. And my tax salary is 1,538,504. Then my salary to be received is my gross minus tax = 12,327,472 which is not the target net. Hope this is clearer.

Yes. And we are using similar terminology. The only difference is: I used the term "taxable inc(ome)" instead of "taxable salary". They mean the same in this context. I will use the term "taxable salary" as well.

But I think you misunderstand the tax and net calculations. I believe I demonstrated the correct calculations in post #18 . That is:

gross (13,865,976) minus deductions (123,456) = taxable salary (13,742,250)

taxable salary (13,742,250) minus tax (1,538,504) = net (12,204,016)

Note that tax is based on taxable salary, not on gross. That is:

taxable salary (13,742,250) times tax rate (20%) minus allowance (1,210,000) = tax (1,538,504)

My goal is if a staff has a child for tax deduction 123,456 and she/he requests a net salary of 12,204,016. How much gross I have to declare to tax authority in order for her/him to get 12,204,016.

First we derive taxable salary (13,742,250) based on the target net (12,204,016).

That calculation is demonstrated by the formula in B3 in post #10 and in B5 in post #18 . They differ only because the cell references changed due to changes in the spreadsheet design.

Again, note that tax is based on taxable salary, not on gross.

Then we calculate gross (13,865,976) by adding deductions (123,456) to the derived taxable salary (13,742,250).

That calculation is demonstrated by the formula in B3 in post #18 .

In post #10 , "taxable salary" was called "gross" because you were not considering pre-tax deductions. That was a misnomer. It should always have been called "taxable salary", even if that is the same as gross by coincidence. And that name change seems to be the source of your confusion.

-----

Errata.... To be consistent in post #18 , I should calculate tax as a negative amount, just as you entered allowance as a negative amount. Alternatively, I should calculate deductions as a positive amount, just as I calculated tax.

Let me know if that is confusing you. I can repost the example in post #18 with consistent signs. Just let me know which you want deductions and tax to be: positive or negative amounts.

FYI, my preference is to enter all amounts, including allowance, as positive amounts, and let the formulas determine whether they are added or subtracted in the calculation.

 

[joeu2004]

Errata....

gross (13,865,976) minus deductions (123,456) = taxable salary (13,742,250)
taxable salary (13,742,250) minus tax (1,538,504) = net (12,204,016)
[....]
taxable salary (13,742,250) times tax rate (20%) minus allowance (1,210,000) = tax (1,538,504)
[....]
Then we calculate gross (13,865,976) by adding deductions (123,456) to the derived taxable salary (13,742,250).

Of course, taxable salary is 13,742,520. A typo. Sigh.

 

[Vanda_a]

Good morning. The different here is the deduction. Here we use deductions to find taxable salary only and we don’t deduct staffs. We deduct only tax on salary. That is why gross minus tax is not equal target net

Thank you

 

[joeu2004]

Sorry, but I cannot help you. Good luck !

 

[joeu2004]

Okay, I will take one more stab at this. I'm afraid that we are not communicating very well. So the following is just a wild guess.

Sometimes, "deductions" (usually called exemptions) are a reduction of tax, not gross. So I wonder if the correct calculation of net salary is the following, given the gross salary.

13,896,840.00  gross
-1,569,368.00  tax on gross = gross*rate - allowance = 13,896,840*20% - 1,210,000
-  123,456.00  deductions
-------------
12,204,016.00  net

If that is correct, the table below demonstrates how to derive gross given deductions (123,456) and target net (12,204,016).

If my calculation above still does not meet your expectations, then I'm afraid I cannot help you further.

Book1
	A	B	C	D	E
2	target net	12,204,016.00
3	gross	13,896,840.00
4	tax	1,569,368.00
5	deductions	123,456.00
6	net	12,204,016.00
7
8	gross over...	to...	rate	allowance	net+deductions over...
9	0.00	1,200,000.00	0%	0.00	0.00
10	1,200,000.00	2,000,000.00	5%	60,000.00	1,080,000.00
11	2,000,000.00	8,500,000.00	10%	160,000.00	1,640,000.00
12	8,500,000.00	12,500,000.00	15%	585,000.00	6,640,000.00
13	12,500,000.00		20%	1,210,000.00	8,790,000.00
Sheet1

B3: =ROUND((B2+B5-INDEX($D$9:$D$13,COUNTIF($E$9:$E$13,"<"&B2+B5))) / (1-INDEX($C$9:$C$13,COUNTIF($E$9:$E$13,"<"&B2+B5))),0)
B4: =ROUND(B3*VLOOKUP(B3,$A$9:$D$13,3) - VLOOKUP(B3,$A$9:$D$13,4),0)
B6: =B3 - B4 - B5
B9: =A10
E9: =A9*(1-C9) - D9

Note the subtle change: all amounts are positive, including the "allowance" (progressive tax offset) in tax rate schedule. The formulas determine when to add or subtract the amounts.

 

[joeu2004]

Sigh, I cannot edit my previous posting because the forum/browser editor screws up the table.

PS....

B3: =ROUND((B2+B5-INDEX($D$9:$D$13,COUNTIF($E$9:$E$13,"<"&B2+B5))) / (1-INDEX($C$9:$C$13,COUNTIF($E$9:$E$13,"<"&B2+B5))),0)
B4: =ROUND(B3*VLOOKUP(B3,$A$9:$D$13,3) - VLOOKUP(B3,$A$9:$D$13,4),0)
B6: =B3 - B4 - B5
B9: =A10
E9: =A9*(1-C9) - D9

Copy B9 into B10:B12.
Copy E9 into E10:E13.

 

[joeu2004]

Sometimes, "deductions" (usually called exemptions) are a reduction of tax, not gross.

Brain fart! They are usually called tax credits.