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.

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

<tbody>
</tbody>

 

[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