Rounding an IF statement

[grcshekar]

Dear Experts

How do I add ROUND,0 to below formula


=IF(AND(M2*12>500000,M2*12<=750000),(12500+(M2*12-500000)*10%)*(1.04/12),IF(AND(M2*12>750000,M2*12<=1000000),(37500+(M2*12-750000)*15%)*(1.04/12),IF(AND(M2*12>1000000,M2*12<=1250000),(75000+(M2*12-1000000)*20%)*(1.04/12),IF(AND(M2*12>1250000,M2*12<=1500000),(125000+(M2*12-1250000)*25%)*(1.04/12),IF(AND(M2*12>1500000),(187500+(M2*12 -1500000)* 30%)*(1.04/12),0)))))

 

[grcshekar]

Thank for all the guidance and timely help.

I am using this without any problems

D2: =ROUND(1.04 * IF(M2*12<=$P$3, 0,
$Q$3+SUMPRODUCT((M2*12>$P$2:$P$7)*(M2*12-$P$2:$P$7), $S$2:$S$7)) / 12, 0

 

[KRice]

Here is another option that takes taxable monthly income, annualizes it by multiplying by 12, and then uses that annual amount in a single formula for calculating the progressive tax. Another formula then applies the 4% additional burden to the tax and rounds the answer. This could all be done in one formula if desired. One question about the tax table...if someone makes less than 500,000, how is the tax computed? I'm assuming it would be at a 2.5% rate, but if not, the formula will need a small adjustment.

MrExcel20200516_b.xlsx
	A	B	C
1	Monthly Taxable Income	Annual Tax	Annual Tax with 4% burden
2	80001	69001.8	71762
Sheet9

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(A2*12>={0;500000;750000;1000000;1250000;1500000}),A2*12-{0;500000;750000;1000000;1250000;1500000},{0.025;0.075;0.05;0.05;0.05;0.05})
C2	C2	=ROUND(B2*1.04,0)

 

[joeu2004]

One question about the tax table...if someone makes less than 500,000, how is the tax computed? I'm assuming it would be at a 2.5% rate

grcshekar already answered that question by implication, and your assumption is incorrect.

grcshekar's original formula implies that there is no tax on 500,000 or less, and 12,500 is added to the progressive tax for taxable income over 500,000.

That is correctly reflected in the formulas that I posted.

However, your question prompted me to research the India income tax procedures. Although I did find information at an incometaxindia.gov.in website (authoritative), I found a clearer explanation at a cleartax.in website (non-authorative).

In effect, it confirms the tax table that grcshekar posted, with tax starting at 12,500 for taxable income over 500,000.

But the actual tax table is as follows:

tiered tax grcshekar.xlsx
	P	Q	R	S
1	over....	tax	+ pct over	diff pct
2	0	0	0%	0%
3	250,000	0	5%	5%
4	500,000	12,500	10%	5%
5	750,000	37,500	15%	5%
6	1,000,000	75,000	20%	5%
7	1,250,000	125,000	25%	5%
8	1,500,000	187,500	30%	5%
Sheet1 (2)

So actually, there is no tax on taxable income of 250,000 or less; and there is a 5% tax on taxable income of 500,000 or less and over 250,000.

Also, 4% of the tax is added for the health and education "cess" (surtax). Presumably, that is why grcshekar multiplied the tax by 1.04.

However, if taxable income is 500,000 or less, the taxpayer can apply for a rebate of up to the first 12,500 in tax. And the rebate applies before adding the 4% "cess", according to cleartax.in.

I don't know India tax procedures. But it appears to me that the rebate is applied for when the taxpayer files a tax return at the end of the tax year.

So perhaps the monthly withholding tax should not take the rebate into account, as grcshekar's original formula did.

To that end, using the full tax table above, the formula for the monthly withholding tax should be:

=ROUND(1.04 * SUMPRODUCT((M2*12>$P$2:$P$8)*(M2*12-$P$2:$P$8), $S$2:$S$8)/12, 0)

(Note that the values in column Q are no longer needed.)

That results in a monthly withholding tax (and 4% "cess") of 542 for a monthly taxable income 31,250 (375,000 annually), instead of zero.

Food for thought, grcshekar.

 

[KRice]

@joeu2004, good work!...and thanks for looking into this further. You mentioned:

In effect, it confirms the tax table that grcshekar posted, with tax starting at 12,500 for taxable income over 500,000.

It was the presence of the fixed amount (12,500) that prompted my question. Often for progressive rate schemes, the developers of the tax tables insert a fixed amount for each band representing the tax owed after filling all lower bands. In this case, I assumed (incorrectly) that there might be one lower band of 0 to 500,000 taxed at 2.5 %, but you've determined there are actually two lower bands: 0 to 250,000 at 0 % and 250,000 to 500,000 at 5 %. Importantly, the original posting did not address this and it was not apparent in the formula.

Here is a revision to my approach that includes correct descriptions of the two lower brackets. Like your method above in post #13, this also uses differential rates, so as one moves from a lower bracket to a higher bracket, the difference in percentage points is what this formula relies upon. The key difference is that this doesn't use the table, but rather incorporates the table content in arrays within the formula.

MrExcel20200516_b.xlsx
	A	B	C
1	Monthly Taxable Income	Annual Tax	Annual Tax with 4% burden
2	80001	69002	71762
Sheet9

Cell Formulas
Range	Formula
B2	B2	=ROUND(SUMPRODUCT(--(A2*12>={0;250000;500000;750000;1000000;1250000;1500000}),A2*12-{0;250000;500000;750000;1000000;1250000;1500000},{0;0.05;0.05;0.05;0.05;0.05;0.05}),0)
C2	C2	=ROUND(1.04*SUMPRODUCT(--(A2*12>={0;250000;500000;750000;1000000;1250000;1500000}),A2*12-{0;250000;500000;750000;1000000;1250000;1500000},{0;0.05;0.05;0.05;0.05;0.05;0.05}),0)

Two versions of the formula are shown. One calculates the tax only, rounded. The other calculates the tax, burdens it by an additional 4 % and then rounds the result. I'm not sure whether @grcshekar wants any additional checks on the amount to reflect the rebate amount.

 

[joeu2004]

you've determined there are actually two lower bands: 0 to 250,000 at 0 % and 250,000 to 500,000 at 5 %. Importantly, the original posting did not address this and it was not apparent in the formula.

Although grcshekar did not point this out explicitly, he did incorporate it into his formulas -- and I mimicked him -- insofar as he included the effect of the rebate.

The only question (that I have) is whether or not it is correct to do so, especially for the monthly withholding tax. That is a question that only grcshekar can answer.

@grcshekar, again my sense of it is: you are expected to pay the full tax monthly or periodically, then apply for a rebate at the end of the tax year, when eligibility can be determined. But of course, I don't know anything about India tax procedures. If you are unsure, you should consult someone who does.

The key difference is that this doesn't use the table, but rather incorporates the table content in arrays within the formula

Right! And IMHO, it is much better to reference a table instead of array constants.

Some other differences that are just oversights:

1. In post #9, grcshekar made it clear that ``i need to divide result by 12 and then round by 0 [decimal places] in order to calculate monthly deductions``. So you need to divide by 12 before rounding.

2. From the beginning, grcshekar has incorporated the 4% "burden" (health and education "cess", I presume) into the calculation, again before rounding, albeit incorrectly (by dividing by 12).

I assume that it is correct to do so on a monthly/period basis, as well. But the phrasing of the explanation of the rebate at cleartax.in leaves me with some doubt. It depends on the details of the rebate procedure, which I am not taking the time to learn.

@grcshekar, again, this is a question that only you can answer.

 

[KRice]

@joeu2004, good point about the rebate. I agree with your sense that the full tax is probably due, and only after reconciling at the end of the year would the rebate be applied for. The implication is that the lower tax brackets should be retained in the calculations...but I'm not familiar with the tax code either, so I'm not sure.

As for the rounding discussion, I understand your point, but I wouldn't say that there are any oversights. My solution was presented as offering a rounded annual tax amount, not a rounded monthly tax amount. The formulas are easily changed to show monthly amounts by dividing the unrounded annual amount by 12, applying any burden if desired, and then rounding.

MrExcel20200516_b.xlsx
	A	B	C
5	Monthly Taxable Income	Monthly Tax	Monthly Tax with 4% burden
6	80001	5750	5980
Sheet9

Cell Formulas
Range	Formula
B6	B6	=ROUND(SUMPRODUCT(--(A6*12>={0;250000;500000;750000;1000000;1250000;1500000}),A6*12-{0;250000;500000;750000;1000000;1250000;1500000},{0;0.05;0.05;0.05;0.05;0.05;0.05})/12,0)
C6	C6	=ROUND(1.04*SUMPRODUCT(--(A6*12>={0;250000;500000;750000;1000000;1250000;1500000}),A6*12-{0;250000;500000;750000;1000000;1250000;1500000},{0;0.05;0.05;0.05;0.05;0.05;0.05})/12,0)