# Employee Tax Calculations

#### rushdhi

##### New Member
I maintain an Excel Spreadsheet to calculate the Gross pay, Statutory contributions and employees tax deductions. Our Tax Table is as follows :

GP = Gross Pay
TA = Tax Amount to be deducted

IF GP < \$21,748, TA=0
IF GP >\$21,748 < 26,042,TA = 9.2% of GP-2000
IF GP >26,042 < 37083, TA = 10% of GP-\$2208
IF GP >37083 < 52083, TA = 20% of GP-\$5917
IF GP > 52083, TA = 35% of GP-\$13729

Our Salaries are calculated on the number of Work days in the month and multiplied by the Exchange Rate prevalent on the date of payment and as such the TA amount differs from one month to the other. what I want EXCEL to do is that when a GP shows up on one Cell for one individual , the TA amount should be automatically displayed in the other cell.

Thanks
Rushdhi

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### BigC

##### Well-known Member
Rushdi
You need to build a tax rates table similar to the following:
GP From, GP To, Total Tax on GP below this level, Marginal Rate on excess
Then use several VLOOKUP functions (using True as the 4th argument in VLOOKUP)in the one formula to find the appropriate income level for the selected GP, and return:
1) the tax on the level below , plus
2) the marginal tax rate for the relevant band (which you multiply by the excess of selected GP over top of previous level)
Sorry I don't have the time right now to do it for you. Have a go at it, and you'll learn a lot about VLOOKUPs.

HTH

Replies
3
Views
10K
Replies
0
Views
359
Replies
2
Views
289
Replies
5
Views
1K
Replies
8
Views
419

1,181,065
Messages
5,927,914
Members
436,576
Latest member
rovman1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back