Gross salary from Net Salary

[emajor]

Hi
please I need urgent excel help to find the gross salary, given the Net salary in Ghana

Net salary( example)....... 2,723.50
S.S.F............................5.5%
P.F...............................3%

NB: (S.S.F. and P.F. are deducted from the gross salary before arriving at chargeable income, on which Income tax is applied)

Income Tax rates as follows:

MONTHLY INCOME TAX RATES APPLICABLE TO RESIDENT INDIVIDUALS

[TABLE="width: 600"]
<tbody>[TR]
[TD="width: 150"]CHARGEABLE INCOME [/TD]
[TD="width: 80"]RATE [/TD]
[TD="width: 112"]TAX [/TD]
[TD="width: 132"]CUMULATIVE CHARGEABLE INCOME [/TD]
[TD="width: 126"]CUMULATIVE TAX [/TD]
[/TR]
[TR]
[TD="width: 150"]GH₵[/TD]
[TD="width: 80"]%[/TD]
[TD="width: 112"]GH₵[/TD]
[TD="width: 132"]GH₵[/TD]
[TD="width: 126"]GH₵[/TD]
[/TR]
[TR]
[TD="width: 150"]First 288[/TD]
[TD="width: 80"]Nil[/TD]
[TD="width: 112"]–[/TD]
[TD="width: 132"]288[/TD]
[TD="width: 126"]–[/TD]
[/TR]
[TR]
[TD="width: 150"]Next 100[/TD]
[TD="width: 80"]5[/TD]
[TD="width: 112"]5[/TD]
[TD="width: 132"]388[/TD]
[TD="width: 126"]5[/TD]
[/TR]
[TR]
[TD="width: 150"]Next 140[/TD]
[TD="width: 80"]10[/TD]
[TD="width: 112"]14[/TD]
[TD="width: 132"]528[/TD]
[TD="width: 126"]19[/TD]
[/TR]
[TR]
[TD="width: 150"]Next 3,000[/TD]
[TD="width: 80"]17.5[/TD]
[TD="width: 112"]525[/TD]
[TD="width: 132"]3,528[/TD]
[TD="width: 126"]544[/TD]
[/TR]
[TR]
[TD="width: 150"]Next 16,472[/TD]
[TD="width: 80"]25[/TD]
[TD="width: 112"]4,118[/TD]
[TD="width: 132"]20,000[/TD]
[TD="width: 126"]4,662[/TD]
[/TR]
[TR]
[TD="width: 150"]Exceeding 20,000[/TD]
[TD="width: 80"]30[/TD]
[TD="width: 112"][/TD]
[TD="width: 132"][/TD]
[TD="width: 126"][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

SSF and PS are both applied to Gross, so a total of 8.5% is deducted to arrive at chargeable?

 

[shg]

Assuming so,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Chargeable​

[/td][td="bgcolor:#F3F3F3"]

Tax​

[/td][td="bgcolor:#F3F3F3"]

Net​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.00​

[/td][td]

0.00​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][td="bgcolor:#CCFFFF"]C2: =A2 - B2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

288.00​

[/td][td]

0.00​

[/td][td="bgcolor:#CCFFFF"]

288.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

388.00​

[/td][td]

5.00​

[/td][td="bgcolor:#CCFFFF"]

383.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

528.00​

[/td][td]

14.00​

[/td][td="bgcolor:#CCFFFF"]

514.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

2,528.00​

[/td][td]

525.00​

[/td][td="bgcolor:#CCFFFF"]

2,003.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

20,000.00​

[/td][td]

4,118.00​

[/td][td="bgcolor:#CCFFFF"]

15,882.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

100,000.00​

[/td][td="bgcolor:#FFFF99"]

28,118.00​

[/td][td="bgcolor:#CCFFFF"]

71,882.00​

[/td][td][/td][td="bgcolor:#FFFF99"]B8: = B7 + 30% * (A8 - A7)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td="bgcolor:#F3F3F3"]

Net​

[/td][td="bgcolor:#F3F3F3"]

Gross​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

2,723.50​

[/td][td="bgcolor:#CCFFCC"]

3,754.12​

[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]B12: =PERCENTILE($A$2:$A$8, PERCENTRANK($C$2:$C$8, A12, 8)) / (1 - 5% - 3.5%)[/td][/tr]
[/table]

 

[emajor]

exactly so

 

[shg]

You're welcome.

 

[emajor]

shg, thanks for your assistance, but I think something is amiss could you look at it again, please?

the gross salary for the Net(2,723.50) I gave is actually 3,510.65.

NB: Your question: SSF and PF are both applied to Gross, so a total of 8.5% is deducted to arrive at chargeable?
My answer: exactly so.

thanks shg, standing by to hear from you soonest.

 

[shg]

Sorry, misunderstood your numbers:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Chargeable Income​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Total Tax​

[/td][td="bgcolor:#F3F3F3"]

Net Income​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0.00​

[/td][td]

0.0%​

[/td][td]

0.00​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][td="bgcolor:#CCFFFF"]D3: =B3 - C3[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

288.00​

[/td][td]

5.0%​

[/td][td="bgcolor:#FFFF99"]

0.00​

[/td][td="bgcolor:#CCFFFF"]

288.00​

[/td][td][/td][td="bgcolor:#FFFF99"]D4: =D3 + C3 * (B4 - B3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

388.00​

[/td][td]

10.0%​

[/td][td="bgcolor:#FFFF99"]

5.00​

[/td][td="bgcolor:#CCFFFF"]

383.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

528.00​

[/td][td]

17.5%​

[/td][td="bgcolor:#FFFF99"]

19.00​

[/td][td="bgcolor:#CCFFFF"]

509.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

3,528.00​

[/td][td]

25.0%​

[/td][td="bgcolor:#FFFF99"]

544.00​

[/td][td="bgcolor:#CCFFFF"]

2,984.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

20,000.00​

[/td][td]

30.0%​

[/td][td="bgcolor:#FFFF99"]

4,662.00​

[/td][td="bgcolor:#CCFFFF"]

15,338.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

100,000.00​

[/td][td][/td][td="bgcolor:#FFFF99"]

28,662.00​

[/td][td="bgcolor:#CCFFFF"]

71,338.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td]

Check​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Net​

[/td][td="bgcolor:#F3F3F3"]

Gross​

[/td][td="bgcolor:#F3F3F3"]

Net​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td][/td][td]

2,723.50​

[/td][td="bgcolor:#CCFFCC"]

3,510.65​

[/td][td="bgcolor:#FFCCCC"]

2,723.50​

[/td][td][/td][td="bgcolor:#CCFFCC"]D13: =PERCENTILE($B$3:$B$9, PERCENTRANK($E$3:$E$9, C13, 8)) / (1 - 5% - 3.5%)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td][/td][td]

^-----​

[/td][td]--- same ---[/td][td]-----^[/td][td][/td][td="bgcolor:#FFCCCC"]E13: =PERCENTILE($E$3:$E$9, PERCENTRANK($B$3:$B$9, D13 * (1 - 5% - 3.5%), 8))[/td][/tr]
[/table]

 

[emajor]

Thanks so much shg for helping me out, you're one in a million

 

[shg]

You're welcome.

 

[Macropod]

Cross-posted at: www.msofficeforums.com/excel/42721-gross-salary-net-salary.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html