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]

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)

Replace SUMPRODUCT(--(B2>$E$8:$E$12)) with COUNTIF($E$8:$E$12,"<"&B2). (Klunk!)

 

[Dave Patton]

Hello Vanda_a;

In your first post, you showed an Allowance.

What is the Allowance and how is it calculated?

 

[joeu2004]

Hello Vanda_a; In your first post, you showed an Allowance. What is the Allowance and how is it calculated?

Good question! Answer: It's another way to do a progressive tax table calculation.

Vanda's tax table appears to be the Cambodian withholding table [1]. I confirmed that it is a progressive tax table, at least for residents.
[1] http://www.tax.gov.kh/en/btos.php

Usually, we calculate a progressive tax by the following manual method:

(gross - lowBracket)*rate + minTax

where minTax is the sum of the max tax for the previous brackets. The table below also shows the same calculation using SUMPRODUCT.

Algebraically, the manual method is the same as:

gross*rate - lowBracket*rate + minTax = gross*rate + allowance

where allowance (negative) = -lowBracket*rate + minTax. Note that gross*rate is the flat tax.

This is demonstrated below.

Book1
	A	B	C	D	E	F	G	H	I	J
3	gross	13,742,520.00						progressive tax	=tax?
4	tax	1,538,504.00					manual	1,538,504.00	TRUE
5	net	12,204,016.00					SUMPRODUCT	1,538,504.00	TRUE
6
7	gross over...	to	rate	allowance	net over...		min tax	flat tax offset	net offset (allowance)	diff rate
8	0.00	1,200,000.00	0%	0	0.00		0.00	0.00	0.00	0%
9	1,200,000.00	2,000,000.00	5%	-60,000.00	1,200,000.00		0.00	-60,000.00	-60,000.00	5%
10	2,000,000.00	8,500,000.00	10%	-160,000.00	1,960,000.00		40,000.00	-200,000.00	-160,000.00	5%
11	8,500,000.00	12,500,000.00	15%	-585,000.00	7,810,000.00		690,000.00	-1,275,000.00	-585,000.00	5%
12	12,500,000.00		20%	-1,210,000.00	11,210,000.00		1,290,000.00	-2,500,000.00	-1,210,000.00	5%
Sheet1

Formulas:
B4: =ROUND(B3*VLOOKUP(B3,$A$8:$D$12,3) + VLOOKUP(B3,$A$8:$D$12,4), 2)
B5: =B3-B4
H4: =(B3 - VLOOKUP(B3,$A$8:$G$12,1))*VLOOKUP(B3,$A$8:$G$12,3) + VLOOKUP(B3,$A$8:$G$12,7)
H5: =ROUND(SUMPRODUCT((B3 > A8:A12)*(B3 - A8:A12), J8:J12), 2)
I4: =H4=$B$4
I5: =H5=$B$4
E8: =A8*(1-C8) - D8
H8: =-A8*C8
I8: =H8+G8
J8: =C8 - N(C7)
G9: =(B8-A8)*C8 + G8
Copy E8 into E9:E12
Copy H8:J8 into H9:J12
Copy G9 into G10:G12

 

[joeu2004]

Late errata (minor):

H4: =(B3 - VLOOKUP(B3,$A$8:$G$12,1))*VLOOKUP(B3,$A$8:$G$12,3) + VLOOKUP(B3,$A$8:$G$12,7)
[....]
E8: =A8*(1-C8) - D8
H8: =-A8*C8
[....]
G9: =(B8-A8)*C8 + G8

Those formulas should have been rounded to 2 decimal places.

 

[Dave Patton]

Good information Joe.

It may be personal preference but I still like the SumProduct formula posted in #2 .

If the known information is the net amount, Excel's Goal Seek can calculate the Gross.

Dave

 

[joeu2004]

If the known information is the net amount, Excel's Goal Seek can calculate the Gross.

No need for Goal Seek or Solver. I demonstrated how to do it with a formula in posts #10 and 11.

PS.... When I wrote, "Usually, we calculate a progressive tax by the following manual method", I meant when we calculate by hand. Yes, I use SUMPRODUCT when I "formulate" the calculation.

 

[Vanda_a]

Hello Joeu

Thank you very much for you help. May you help me further please? above one works well.
I would like to add whatever amount to the generated gross and would like to keep the net the same.

 

[joeu2004]

above one works well. I would like to add whatever amount to the generated gross and would like to keep the net the same.

That could be as simple as changing terminology.

What we calculated previously should be called "taxable income" (TI), such that:

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

G = TI - D

where D=deductions (negative), G = gross, N=net, A=allowance (negative), r=tax rate.

But in that case, expected gross [sic] cannot be 13,742,520, as you wrote initially.

Instead, expected taxable income is 13,742,520, and expected gross is something larger, namely taxable income plus deductions (positive).

IMHO, it is really confusing when you enter allowances, and now deductions (I presume), as negative numbers.

This is demonstrated below.

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

B3: =B5 - B4
B5: =ROUND((B2 + INDEX($D$10:$D$14,COUNTIF($E$10:$E$14,"<"&B2))) / (1 - INDEX($C$10:$C$14,COUNTIF($E$10:$E$14,"<"&B2))), 0)
B6: =ROUND(B5*VLOOKUP(B5,$A$10:$D$14,3) + VLOOKUP(B5,$A$10:$D$14,4), 0)
B7: =B5 - B6
E7: =(B7=B2)
E10: =A10*(1-C10) - D10
Copy E10 into E11:E14

By the way, did you notice the subtle change that I made (previously as well) in column A in the tax rate table? For example, it is "over..." 1,200,000 instead of "from..." 1,200,001. That could make a difference of some "cents" (1/100 unit currency) in taxes, unless taxable income is always rounded to the unit currency. IMHO, the Cambodian tax rate schedule is ambiguous for the amounts between 1,200,000.01 and 1,200,000.99 inclusive, since the previous tax bracket is only "to..." 1,200,000.

 

[Vanda_a]

Good morning Dave.

This allowance is for deduction after the gross multiply the rate.

Thank you

 

[Vanda_a]

That could be as simple as changing terminology.

Awesome.. You know the rate is from Cambodia.
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.

hank you