Augustinraj.a
New Member
Dear Ji,
Good Afternoon,
Hope you are fine and safe with family!
I need your support to complete the IF condition for our tax working since I want to bring all the condition into one sheet that’s what I approach you since I tried to the extent possible but not able to complete the same.
As you aware there are new tax regimes in place where employee can choose OLD Regime or NEW Regime at the same time if employee chooses any one cannot be change until the FY close.
Our Tax sheet should work like below.
If we select “H3” as OLD then the following formula should work in the column of “BD3”
=ROUND(IF(((TODAY()E3)/365)<60,IF(BC3<250000,0,IF(BC3<=500000,((BC3250000)*5%),IF(BC3<=1000000,((BC3500000)*20%+12500),((BC31000000)*30%)+112500))),IF(((TODAY()E3)/365)>80,IF(BC3<500000,0,IF(BC3<=1000000,((BC3500000)*20%),((BC31000000)*30%)+100000)),IF(BC3<300000,0,IF(BC3<=500000,((BC3300000)*5%),IF(BC3<=1000000,((BC3500000)*20%+10000),((BC31000000)*30%)+110000))))),0)
If we select “H3” as NEW then the following formula should work in the column of “BD3”
(But this formula has the error due to some open n close () this missing am not able to find)
=ROUND(IF(((TODAY()E3)/365)<60,IF(BB3<=250000,0,IF(BB3<=500000,((BB3250000)*5%),IF(BB3<=750000,((BB3500000)*10%+12500),IF(BB3<=1000000,((BB3750000)*20%+37500),IF(BB3<=1250000,((BB31000000)*25%+75000),IF(BB3<=1500000,((BB31250000)*30%+125000),((BB31500000)*30%+187500)))))))),0),IF(((TODAY()E3)/365)>80,IF(BB3<=500000,0,IF(BB3<=750000,((BB3500000)*10%),IF(BB3<=1000000,((BB3750000)*15%+25000),IF(BB3<=1250000,((BB31000000)*20%+62500),IF(BB3<=1500000,((BB31250000)*25%+112500),((BB31500000)*30%+175000)),IF(BB3<=300000,0,IF(BB3<=500000,((BB3300000)*5%),IF(BB3<=750000,((BB3500000)*10%+10000),IF(BB3<=1000000,((BB3750000)*15%+35000),IF(BB3<=1250000,((BB31000000)*20%+72500),IF(BB3<=1500000,((BB31250000)*25%+122500),((BB31500000)*30%+185000))))))))))))),0)
AND
If we select “H3” as NEW then the following formula should work in the below columns
BA3 should be consider only (I3AX3) because as per the new regime there is no exemption allowed in any category except 80CCD (2) NPS.
Please help me to solve this issue JI, thanks for the help
Good Afternoon,
Hope you are fine and safe with family!
I need your support to complete the IF condition for our tax working since I want to bring all the condition into one sheet that’s what I approach you since I tried to the extent possible but not able to complete the same.
As you aware there are new tax regimes in place where employee can choose OLD Regime or NEW Regime at the same time if employee chooses any one cannot be change until the FY close.
Our Tax sheet should work like below.
If we select “H3” as OLD then the following formula should work in the column of “BD3”
=ROUND(IF(((TODAY()E3)/365)<60,IF(BC3<250000,0,IF(BC3<=500000,((BC3250000)*5%),IF(BC3<=1000000,((BC3500000)*20%+12500),((BC31000000)*30%)+112500))),IF(((TODAY()E3)/365)>80,IF(BC3<500000,0,IF(BC3<=1000000,((BC3500000)*20%),((BC31000000)*30%)+100000)),IF(BC3<300000,0,IF(BC3<=500000,((BC3300000)*5%),IF(BC3<=1000000,((BC3500000)*20%+10000),((BC31000000)*30%)+110000))))),0)
If we select “H3” as NEW then the following formula should work in the column of “BD3”
(But this formula has the error due to some open n close () this missing am not able to find)
=ROUND(IF(((TODAY()E3)/365)<60,IF(BB3<=250000,0,IF(BB3<=500000,((BB3250000)*5%),IF(BB3<=750000,((BB3500000)*10%+12500),IF(BB3<=1000000,((BB3750000)*20%+37500),IF(BB3<=1250000,((BB31000000)*25%+75000),IF(BB3<=1500000,((BB31250000)*30%+125000),((BB31500000)*30%+187500)))))))),0),IF(((TODAY()E3)/365)>80,IF(BB3<=500000,0,IF(BB3<=750000,((BB3500000)*10%),IF(BB3<=1000000,((BB3750000)*15%+25000),IF(BB3<=1250000,((BB31000000)*20%+62500),IF(BB3<=1500000,((BB31250000)*25%+112500),((BB31500000)*30%+175000)),IF(BB3<=300000,0,IF(BB3<=500000,((BB3300000)*5%),IF(BB3<=750000,((BB3500000)*10%+10000),IF(BB3<=1000000,((BB3750000)*15%+35000),IF(BB3<=1250000,((BB31000000)*20%+72500),IF(BB3<=1500000,((BB31250000)*25%+122500),((BB31500000)*30%+185000))))))))))))),0)
AND
If we select “H3” as NEW then the following formula should work in the below columns
BA3 should be consider only (I3AX3) because as per the new regime there is no exemption allowed in any category except 80CCD (2) NPS.
Please help me to solve this issue JI, thanks for the help
Attachments

24.4 KB Views: 2