PlanMyTax

Income Tax calculations in excel without if statements

For Financial Year 2024-25 under New Regime the Formula is as below:

=MAX(0, MIN(A1-300000, 400000)*0.05)

+ MAX(0, MIN(A1-700000, 300000)*0.10)

+ MAX(0, MIN(A1-1000000, 200000)*0.15)

+ MAX(0, MIN(A1-1200000, 300000)*0.20)

+ MAX(0, (A1-1500000)*0.30)

Here's the breakdown of the formula:

1. Income ≤ 300,000: Tax is 0.
2. 300,000 < Income ≤ 700,000: MAX(0, MIN(A1-300000, 400000)*0.05)
   • This part calculates the tax for the income between 300,000 and 700,000 (5% of the amount exceeding 300,000).
3. 700,000 < Income ≤ 1,000,000: MAX(0, MIN(A1-700000, 300000)*0.10)
   • This part calculates the tax for the income between 700,000 and 1,000,000 (10% of the amount exceeding 700,000).
4. 1,000,000 < Income ≤ 1,200,000: MAX(0, MIN(A1-1000000, 200000)*0.15)
   • This part calculates the tax for the income between 1,000,000 and 1,200,000 (15% of the amount exceeding 1,000,000).
5. 1,200,000 < Income ≤ 1,500,000: MAX(0, MIN(A1-1200000, 300000)*0.20)
   • This part calculates the tax for the income between 1,200,000 and 1,500,000 (20% of the amount exceeding 1,200,000).
6. Income > 1,500,000: MAX(0, (A1-1500000)*0.30)
   • This part calculates the tax for the income above 1,500,000 (30% of the amount exceeding 1,500,000).

You can place this formula in a cell to calculate the tax based on the income in cell A1.

This formula uses the MAX function to ensure that no negative values are calculated and the MIN function to limit the taxable amount for each bracket.

Dated: Fri 02.08.2024