Employee Tax Calculations

[rushdhi]

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

 

[BigC]

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