 # I need a formula for this

the amount is 100,000. i want 7% from first 30,000, and 11% from second 30,000 and 15% from third 30,000

how can i write a formula that will always work even when the amount is lower than 100,000

10 Replies

# Re: I need a formula for this

I have moved this from the Community Lounge to the Excel forum.

You don't specify what should happen with the last 10,000. In the following, I have assumed that the rate is 19%, but you can easily change that. I created a rate table in D2:F5.

The formula in B2 is

=SUMPRODUCT((A2>\$D\$2:\$D\$5)*(A2-\$D\$2:\$D\$5),\$F\$2:\$F\$5)

If you have Microsoft 365 or Office 2021, you can use

=SUM((A2>\$D\$2:\$D\$5)*(A2-\$D\$2:\$D\$5)*\$F\$2:\$F\$5)

See the attached demo.

# Re: I need a formula for this

thank you @Hans Vogelaar

# Re: I need a formula for this

Hello, Can you assist again in generating formula for the below scenario

first 20,000 7%

Next 20,000 11%

Next 41,666 15%

Next 41,666 19%

Next 133,333 21%

Above 266,666 24%

Again, thanks for the help

# Re: I need a formula for this

The first five added together come to 256,666, not 266,666

# Re: I need a formula for this

yes its true, but the tax law in my country goes like this

# Re: I need a formula for this

``=A1*0.07 + MAX(0, (A1-20000)*0.04) + MAX(0, (A1-40000)*0.04) + MAX(0, (A1-81666)*0.04) + MAX(0, (A1-123332)*0.02) + MAX(0, (A1-266666)*0.03)``

Basically this adds the additional % to the amounts over each threshold.

alternately

``=MMULT(IF(A1>{0,20000,40000,81666,123332,266666},A1-{0,20000,40000,81666,123332,266666},0),{0.07;0.04;0.04;0.04;0.02;0.03})``

or

``````=LET(in,A1,
thresh,{0,20000,40000,81666,123332,266666},
rateInc,{0.07;0.04;0.04;0.04;0.02;0.03},
MMULT(IF(in>thresh,in-thresh,0),rateInc))``````

Looking back at @Hans Vogelaar  prior answer these are nearly identical in concept and it is better to have those values in a table on your sheet so you can easily see it and change it when needed so I recommend his version.

# Re: I need a formula for this

@Habib_Taan

so it's exactly the same as in my original reply, but with an expanded lookup table.

# Re: I need a formula for this

So here is my challenge,

I assume amount in question is 87,333.33
first 25,000 tax on it will be 7% = 1,750
Next 25,000 tax on it will be 11% = 2,750
Third and last amount i have a remaining of 37,333.33 (50,000 minus 87,333.33) that will be taxed at 15% = 5,599.99
Total will be 10,099.99
but with formula provided if I put 87,333.33, I will have 10,926.67 instead
Note that first amount is 25,000 at 7% and second as well is 25,000 at 11% and not 20,000 as i sent it earlier
I apologies if I am wasting your time and thank you in advance

# Re: I need a formula for this

That is because you provided incorrect information, as you mention yourself. You wrote "first 20,000 7% Next 20,000 11%" but now it turns out to be 25000 instead 20000.

# Re: I need a formula for this

Thank you it woked