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>

 

[Vanda_a]

Good evening. I always confuse you. The deduction 123,456 we use to reduce taxable salary in order to reduce tax of salary. If gross 1000$ has to pay tax 100$, then I can use the deduction to pay tax only 50$. If my salary is 1000$ and I have 10 children so my deduction will be high then I got a tax free. Something like that.
(Gross - allowance) x rate = tax.
Gross - tax = net
ex: (1000gross - 1000deduction) x 0 rate = 0 tax.
1000gross - 0 tax = 1000net.
Sorry for my bad English. Thank you very much for you help so far.
I am really appreciate.

 

[joeu2004]

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

But okay, given the target net, you want to calculate the gross such that:

tax = (gross - deductions)*rate - allowance

and

gross - tax = target net

The following demonstrates those calculations.

Book1
	A	B	C	D
2	target net	12,204,016.00
3	deductions	123,456.00
4
5	gross	13,711,656.00
6	deductions	-123,456.00
7	taxable salary	13,588,200.00
8	tax	1,507,640.00
9
10	gross	13,711,656.00
11	tax	-1,507,640.00
12	net	12,204,016.00
13
14	taxable salary over...	to...	rate	allowance
15	0.00	1,200,000.00	0.00%	0.00
16	1,200,000.00	2,000,000.00	5.00%	60,000.00
17	2,000,000.00	8,500,000.00	10.00%	160,000.00
18	8,500,000.00	12,500,000.00	15.00%	585,000.00
19	12,500,000.00		20.00%	1,210,000.00
Sheet1

B5:  { =MAX((B2 - B3*$C$15:$C$19 - $D$15:$D$19) / (1 - $C$15:$C$19)) }
B6:  =-B3
B7:  =MAX(0, B5+B6)
B8:  =B7*VLOOKUP(B7,$A$15:$D$19,3) - VLOOKUP(B7,$A$15:$D$19,4)
B10: =B5
B11: =-B8
B12: =B10+B11
B15: =A16
Copy B15 into B16:B18

The formula in B5 is array-entered. Do not type the curly braces. Instead, press ctrl+shift+Enter instead of just Enter.

Again, note the subtle design change: all constants, including "allowance", are entered as positive values. I let the formulas determine whether to add or subtract, and what sign the result should be.

I would round or round-up some calculations. But I don't want to obscure the design with real-world considerations.

 

[Dave Patton]

A few examples that reflect your latest information.

Note 1
Value required. This is
- input for Excel's Goal Seek see Data | What-if | Goal Seek or
-input for VBA that runs the Goal Seek to complete the calculations.

Excel 2010
	A	B	C	D	E	F
1	Gross	Credits	Taxable	Tax	Net	Goal Required (note 1)
2
3	13,711,656.00	123,456.00	13,588,200.00	1,507,640.00	12,204,016.00	12,204,016.00
4	1,200,095.00	123,456.00	1,076,639.00	0.00	1,200,095.00	1,200,095.00
5	1,993,502.32	123,456.00	1,870,046.32	33,502.32	1,960,000.00	1,960,000.00
6	8,486,282.67	123,456.00	8,362,826.67	676,282.67	7,810,000.00	7,810,000.00
7	12,478,213.65	123,456.00	12,354,757.65	1,268,213.65	11,210,000.00	11,210,000.00
8	23,456,636.00	123,456.00	23,333,180.00	3,456,636.00	20,000,000.00	20,000,000.00
4d

Cell Formulas
Range	Formula
C3	=A3-B3
D3	=SUMPRODUCT(--(C3>rB),C3-rB,rDiff)
E3	=A3-D3

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

 

[Vanda_a]

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

Awesome. It works correctly and perfectly. Thank you very much.

 

[Vanda_a]

A few examples that reflect your latest information.

Hello Dave. Your formula look simple and easy to understand but the column G and I are missing so I could not get the formula work.
Thank you

 

[Dave Patton]

The Table with the tax brackets and rates was shown on post # 2.

I will post the table again.

Excel 2010
	G	H	I
1		Tiered tax
2	Bracket	Rate	rDiff
3	0.00	0%	0%
4	1,200,000.00	5%	5%
5	2,000,000.00	10%	5%
6	8,500,000.00	15%	5%
7	12,500,000.00	20%	5%
8
4d

Cell Formulas
Range	Formula
I3	=H3-N(H2)

 

[joeu2004]

@Vanda.... You're welcome. If you prefer to use SUMPRODUCT to calculate the tax in B8 (I do), see the minor improvement in the table below.

Initially, I was going to present a SUMPRODUCT formula myself, since I prefer it. But I thought you would feel more comfortable with a formula that is similar to your calculation, namely: tax = taxableAmount*rate - allowance.

Ostensibly, your calculation seems to treat the tax rate like a flat tax. The SUMPRODUCT formula treats the tax table like a progressive tax. In post #13 , I demonstrated that your "flat tax" method is indeed another way to calculate the progressive tax; and I cited an authorative webpage [1] that does indeed describe the Cambodia income tax (actually withholding [2]) as a progressive tax (for residents).

[1] http://www.tax.gov.kh/en/btos.php

[2] For the record, I suspect that the difference between withholding tax and actual tax calculations explains the two different methods, effectively, that you use to calculate after-tax income ("net"), namely: (1) actualNet = gross - deductions - actualTax; and (2) takeHomeNet = gross - withholdingTax. If I am correct, I believe the goal should be to derive gross based on withholdingTax = gross*rate - allowance, not based on actualTax = (gross - deductions)*rate - allowance. I suspect the empoyer will calculate withholdingTax, and your goal should be to ensure that the employee receives a specified takeHomeNet. Something for your consideration. If you still believe I am wrong, so be it. No need for you to convince me.

Book1
	A	B	C	D	E
2	target net	12,204,016.00
3	deductions	123,456.00
4
5	gross	13,711,656.00
6	deductions	-123,456.00
7	taxable salary	13,588,200.00
8	tax	1,507,640.00
9
10	gross	13,711,656.00
11	tax	-1,507,640.00
12	net	12,204,016.00
13
14	taxable salary over...	to...	rate	allowance	diff rate
15	0.00	1,200,000.00	0.00%	0.00	0.00%
16	1,200,000.00	2,000,000.00	5.00%	60,000.00	5.00%
17	2,000,000.00	8,500,000.00	10.00%	160,000.00	5.00%
18	8,500,000.00	12,500,000.00	15.00%	585,000.00	5.00%
19	12,500,000.00		20.00%	1,210,000.00	5.00%
Sheet1

Note the addition of E14:E19, highlighted in yellow above.

Formula changes are highlighted in red below.

B5:  { =MAX((B2 - B3*$C$15:$C$19 - $D$15:$D$19) / (1 - $C$15:$C$19)) }
B6:  =-B3
B7:  =MAX(0, B5+B6)
B8:  =SUMPRODUCT((B7 > $A$15:$A$19)*(B7 - $A$15:$A$19), $E$15:$E$19)
B10: =B5
B11: =-B8
B12: =B10+B11
B15: =A16
E15: =C15-N(C14)
Copy B15 into B16:B18
Copy E15 into E16:E19

NOTE: The formula in B5 is array-entered. Do not type the curly braces. Instead, press ctrl+shift+Enter instead of just Enter.

 

[Dave Patton]

Initial information

Excel 2010
	A	B	C	D	E	F
1	Gross	Credits	Taxable	Tax	Net	Goal Required (note 1)
2
3	0.00	123,456.00	-123,456.00	0.00	0.00	12,204,016.00
4	0.00	123,456.00	-123,456.00	0.00	0.00	1,200,095.00
5	0.00	123,456.00	-123,456.00	0.00	0.00	1,960,000.00
6	0.00	123,456.00	-123,456.00	0.00	0.00	7,810,000.00
7	0.00	123,456.00	-123,456.00	0.00	0.00	11,210,000.00
8	0.00	123,456.00	-123,456.00	0.00	0.00	20,000,000.00
4d

Cell Formulas
Range	Formula
C3	=A3-B3
D3	=SUMPRODUCT(--(C3>rB),C3-rB,rDiff)
E3	=A3-D3

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

Run the VBA Goal Seek instead of completing the Goal Seek manually for each row.
See an example of the VBA below.

Results

Excel 2010
	A	B	C	D	E	F
1	Gross	Credits	Taxable	Tax	Net	Goal Required (note 1)
2
3	13,711,656.00	123,456.00	13,588,200.00	1,507,640.00	12,204,016.00	12,204,016.00
4	1,200,095.00	123,456.00	1,076,639.00	0.00	1,200,095.00	1,200,095.00
5	1,993,502.32	123,456.00	1,870,046.32	33,502.32	1,960,000.00	1,960,000.00
6	8,486,282.67	123,456.00	8,362,826.67	676,282.67	7,810,000.00	7,810,000.00
7	12,478,213.65	123,456.00	12,354,757.65	1,268,213.65	11,210,000.00	11,210,000.00
8	23,456,636.00	123,456.00	23,333,180.00	3,456,636.00	20,000,000.00	20,000,000.00
4d

Cell Formulas
Range	Formula
C3	=A3-B3
D3	=SUMPRODUCT(--(C3>rB),C3-rB,rDiff)
E3	=A3-D3

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

Sub GoalSeek()
Dim i As Long
For i = 3 To 8 'edit to reflect the start row number and the the last row number you require
Range("E" & i).GoalSeek Goal:=Range("F" & i).Value, ChangingCell:=Range("A" & i)
Next
End Sub

 

[Vanda_a]

@Vanda.... You're welcome.

Thanks again. It also works for me.

 

[Vanda_a]

Initial information

Thank you for your help