bobitybobbob
New Member
- Joined
- Feb 5, 2019
- Messages
- 3
Dear all, Need some guidance calculating taxes based on fixed slabs.
Ive attempted creating a table with the three columns below and tried to work out a logic based on VLOOKUP but am unable to get the formula setup right.
I have salaries at varying levels and I am trying to create a workbook where I enter the monthly amount and get the tax. Note the slabs below are based on an annual total.
Slabs are
[TABLE="width: 758"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]1
[/TD]
[TD] 400,000.00
[/TD]
[TD][/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 400,001.00
[/TD]
[TD] to 800,000.00
[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] 800,001.00
[/TD]
[TD] to 1,200,000.00
[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] 1,200,001.00
[/TD]
[TD] to 2,500,000.00
[/TD]
[TD]5% of amount exceeding Rs 1,200,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] 2,500,001.00
[/TD]
[TD] to 4,000,000.00
[/TD]
[TD]65,000 + 15% of amount exceeding Rs 2,500,000
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] 4,000,001.00
[/TD]
[TD] to 8,000,000.00
[/TD]
[TD]290,000 +20% of amount exceeding Rs 4,000,000
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] 8,000,001.00
[/TD]
[TD] to and above
[/TD]
[TD]1,090,000 +25% of amount exceeding Rs 8,000,000
[/TD]
[/TR]
</tbody>[/TABLE]
When calculating the conditions are:
If the annual salary is under 400,000 apply zero tax.
If the annual salary is under 800,000 apply 1000 tax.
If the annual salary is under 1,200,000 apply 2000 tax.
If the annual salary is under 2,500,000 apply 2000 on 1,200,000 and 5% on the remaining
If the annual salary s under 4,000,000 apply 65,000 + 15% on remaining above 4,000,000
Many thanks. And firstime poster here so go easy on me
Ive attempted creating a table with the three columns below and tried to work out a logic based on VLOOKUP but am unable to get the formula setup right.
I have salaries at varying levels and I am trying to create a workbook where I enter the monthly amount and get the tax. Note the slabs below are based on an annual total.
Slabs are
[TABLE="width: 758"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]1
[/TD]
[TD] 400,000.00
[/TD]
[TD][/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] 400,001.00
[/TD]
[TD] to 800,000.00
[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] 800,001.00
[/TD]
[TD] to 1,200,000.00
[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] 1,200,001.00
[/TD]
[TD] to 2,500,000.00
[/TD]
[TD]5% of amount exceeding Rs 1,200,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] 2,500,001.00
[/TD]
[TD] to 4,000,000.00
[/TD]
[TD]65,000 + 15% of amount exceeding Rs 2,500,000
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] 4,000,001.00
[/TD]
[TD] to 8,000,000.00
[/TD]
[TD]290,000 +20% of amount exceeding Rs 4,000,000
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] 8,000,001.00
[/TD]
[TD] to and above
[/TD]
[TD]1,090,000 +25% of amount exceeding Rs 8,000,000
[/TD]
[/TR]
</tbody>[/TABLE]
When calculating the conditions are:
If the annual salary is under 400,000 apply zero tax.
If the annual salary is under 800,000 apply 1000 tax.
If the annual salary is under 1,200,000 apply 2000 tax.
If the annual salary is under 2,500,000 apply 2000 on 1,200,000 and 5% on the remaining
If the annual salary s under 4,000,000 apply 65,000 + 15% on remaining above 4,000,000
Many thanks. And firstime poster here so go easy on me