Employee Tax Calculations

rushdhi

New Member
Joined
Sep 23, 2002
Messages
4
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Forum statistics

Threads
1,144,220
Messages
5,723,085
Members
422,477
Latest member
pete101

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top