Computing Gross Income From Net income

[Chigozie]

Hello forum members. I am totalling clueless on how to built a template that computes the gross income from known net income. The details below is how the net income is determine using the Revenue graduated tax band. Here a gross income 100,000 is used. I hope some one can assist.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Band[/TD]
[TD]Rate[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gross Income:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monthly[/TD]
[TD][/TD]
[TD][/TD]
[TD]100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Annual[/TD]
[TD][/TD]
[TD][/TD]
[TD]1,200,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fiscal Reliefs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Consolidated Relief Allowance[/TD]
[TD][/TD]
[TD][/TD]
[TD]440,000[/TD]
[TD][/TD]
[TD]<-- 200,000 or 1% of Gross Income whichever is higher plus 20% of Gross income[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]National Pension Scheme[/TD]
[TD][/TD]
[TD]8%[/TD]
[TD]96,000[/TD]
[TD][/TD]
[TD]8% of Gross Income[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Reliefs[/TD]
[TD][/TD]
[TD][/TD]
[TD]536,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Taxable Income[/TD]
[TD][/TD]
[TD][/TD]
[TD]664,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tax Computation[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD]300,000[/TD]
[TD]7%[/TD]
[TD]21,000[/TD]
[TD][/TD]
[TD]664,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next[/TD]
[TD]300,000[/TD]
[TD]11%[/TD]
[TD]33,000[/TD]
[TD][/TD]
[TD]364,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next[/TD]
[TD]500,000[/TD]
[TD]15%[/TD]
[TD]9,600[/TD]
[TD][/TD]
[TD]64,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next[/TD]
[TD]500,000[/TD]
[TD]19%[/TD]
[TD][/TD]
[TD][/TD]
[TD](436,000)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Next[/TD]
[TD]1,600,000[/TD]
[TD]21%[/TD]
[TD][/TD]
[TD][/TD]
[TD](936,000)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Above 3,200,000[/TD]
[TD]24%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Annual Chargeable Tax[/TD]
[TD][/TD]
[TD][/TD]
[TD]63,600[/TD]
[TD][/TD]
[TD]<--- tax computed or 1% of Gross Income whichever is higher[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Payslip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gross
[/TD]
[TD][/TD]
[TD][/TD]
[TD]100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pension[/TD]
[TD][/TD]
[TD][/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD][/TD]
[TD][/TD]
[TD]5,300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Net Pay[/TD]
[TD][/TD]
[TD][/TD]
[TD]86,700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[radian89]

Hi Chigozie,

using goal seek, located in Data > What if Analysis (in ForeCast section) > goal seek

Set Cell > Net pay

To value > your desirable value

By changing cell > 100.000 gross income monthly


P.S all calculation to get the net Pay, must involve/ formula relate with gross income monthly 100k, otherwise it won't work.

example : consolidated relief allwnce : 1% +20% must refer to 100k ; annual 1200k must from 100k * 12

 

[Chigozie]

Thanks radain89. Though my excel knowledge is limited, I will certainly give it a try as you have detailed. I am grateful.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1
2		Gross Income	Pensions	Tax Reliefs	Tax	Net Income
3		1,200,000.00	96,000.00	536,000.00	63,600.00	1,040,400.00
4	Monthly	100,000.00	8,000.00		5,300.00	86,700.00
5
6
7				Brackets	Rate	Rate_Delta
8				0	7%	7%
9				300,000	11%	4%
10				600,000	15%	4%
11				1,100,000	19%	4%
12				1,600,000	21%	2%
13				3,200,000	24%	3%
14
1d

Cell Formulas
Range	Formula
C3	=B3*0.08
D3	=MAX(200000,B3*0.01)+B3*0.2+C3
E3	=SUMPRODUCT(--((B3-D3)>$D$8:$D$13),(B3-D3)-$D$8:$D$13,$F$8:$F$13)
F3	=B3-E3-C3
F8	=E8-N(E7)
B4	=B3/12

Goal Seek
- change F4 to say 100,000
- by changing B3

 

[Chigozie]

Hi Dave, thanks for your contribution. I encountered challenges while using your method. The goal seek returns the notification "Cell must contain formula". The cells without formula are D4, and E4. should I drawn down the formula in cell D3 and E3?

 

[Dave Patton]

Cell F4 must have the formula F3/12 before running the Goal Seek.

Goal Seek
- change F4 to say 100,000
- by changing B3