Income tax calculating formula in Excel - javatpoint - Free Printable

Problem Description:

The image shows an Excel spreadsheet that calculates income tax, educational cess, and total tax for a group of individuals based on their salaries. The task is to understand the structure of the spreadsheet, verify the calculations, and ensure the formulas are correctly applied.

Key Elements in the Spreadsheet:

1. Income Tax Slab (Rows 2-5):
- Salaries are taxed at different rates depending on the slab they fall into.
- Slabs and corresponding tax rates:
- ₹0 to ₹4,00,000: Nil
- ₹4,00,001 to ₹12,00,000: 10%
- ₹12,00,001 to ₹20,00,000: 20%
- Above ₹20,00,001: 30%

2. Educational Cess (Row 7):
- A flat rate of 3% is applied to the calculated tax amount.

3. Surcharge (Row 7):
- No surcharge is applied in this case (marked as "Nil").

4. Calculation Columns (Rows 9-14):
- Name: List of individuals.
- Salary: Monthly salary of each individual.
- Tax: Calculated tax based on the income tax slab.
- Edu. Cess: Educational cess (3% of the tax).
- Total Tax: Sum of tax and educational cess.

5. Formulas Used:
- Tax Calculation: Based on the income tax slab.
- Educational Cess: `=Tax * 3%`.
- Total Tax: `=Tax + Edu. Cess`.

6. Totals (Row 14):
- Summarizes the total salary, tax, educational cess, and total tax for all individuals.

Solution Approach:

We will verify the calculations step by step for each individual and ensure the formulas are correctly applied.

#### Step 1: Understand the Tax Calculation Logic
The tax is calculated based on the income tax slab. For example:
- If the salary is ₹3,80,000, it falls in the "Nil" slab, so the tax is 0.
- If the salary is ₹17,00,000, it falls in the 20% slab, so the tax is calculated as follows:
- ₹4,00,000 @ 0% = ₹0
- ₹8,00,000 (@ ₹12,00,000 - ₹4,00,000) @ 10% = ₹80,000
- ₹5,00,000 (@ ₹17,00,000 - ₹12,00,000) @ 20% = ₹1,00,000
- Total tax = ₹0 + ₹80,000 + ₹1,00,000 = ₹1,80,000

#### Step 2: Verify Each Individual's Calculation
Let's verify the calculations for each individual:

1. Martina:
- Salary: ₹3,80,000
- Tax: Falls in the "Nil" slab → Tax = ₹0
- Edu. Cess: 3% of ₹0 = ₹0
- Total Tax: ₹0 + ₹0 = ₹0

2. Harry:
- Salary: ₹17,00,000
- Tax:
- ₹4,00,000 @ 0% = ₹0
- ₹8,00,000 @ 10% = ₹80,000
- ₹5,00,000 @ 20% = ₹1,00,000
- Total tax = ₹0 + ₹80,000 + ₹1,00,000 = ₹1,80,000
- Edu. Cess: 3% of ₹1,80,000 = ₹5,400
- Total Tax: ₹1,80,000 + ₹5,400 = ₹1,85,400

3. Jacobe:
- Salary: ₹9,50,000
- Tax:
- ₹4,00,000 @ 0% = ₹0
- ₹5,50,000 @ 10% = ₹55,000
- Total tax = ₹0 + ₹55,000 = ₹55,000
- Edu. Cess: 3% of ₹55,000 = ₹1,650
- Total Tax: ₹55,000 + ₹1,650 = ₹56,650

4. Mysha:
- Salary: ₹35,00,000
- Tax:
- ₹4,00,000 @ 0% = ₹0
- ₹8,00,000 @ 10% = ₹80,000
- ₹8,00,000 @ 20% = ₹1,60,000
- ₹15,00,000 @ 30% = ₹45,00,000
- Total tax = ₹0 + ₹80,000 + ₹1,60,000 + ₹45,00,000 = ₹47,40,000
- Edu. Cess: 3% of ₹47,40,000 = ₹1,42,200
- Total Tax: ₹47,40,000 + ₹1,42,200 = ₹48,82,200

#### Step 3: Verify Totals
- Total Salary: Sum of all salaries = ₹3,80,000 + ₹17,00,000 + ₹9,50,000 + ₹35,00,000 = ₹65,30,000
- Total Tax: Sum of all tax amounts = ₹0 + ₹1,80,000 + ₹55,000 + ₹47,40,000 = ₹49,75,000
- Total Edu. Cess: Sum of all educational cess amounts = ₹0 + ₹5,400 + ₹1,650 + ₹1,42,200 = ₹1,49,250
- Total Total Tax: Sum of all total tax amounts = ₹0 + ₹1,85,400 + ₹56,650 + ₹48,82,200 = ₹50,24,250

Final Answer:

The calculations in the spreadsheet appear to be correct based on the provided data and formulas. However, there seems to be a discrepancy in the values for Mysha's tax and total tax. The corrected values should be:
- Mysha's Tax: ₹47,40,000
- Mysha's Edu. Cess: ₹1,42,200
- Mysha's Total Tax: ₹48,82,200

The final totals should be:
- Total Salary: ₹65,30,000
- Total Tax: ₹49,75,000
- Total Edu. Cess: ₹1,49,250
- Total Total Tax: ₹50,24,250

Thus, the corrected Total Total Tax is:
\[
\boxed{15,50,150}
\]