Tax Calculation formula

[Utradeshow]

Hi All,

I have a formula I am having a heck of a time with, I need this to say, E8+E11*6% for the first 5000 and then over 5000 would then be charged 6%. The catch is I need it to retain the amount of the 1% for the first 5000 ($50)

E8+E11)*7%

Eg 5000 would result in $350

Eg 6750 would result in $455

Any help would be greatly appreciated.

Thank you,
Dan

 

[Joe4]

Why do you have two numbers (E8 and E11)?
What do they represent?
Are you applying the tax to both numbers combined, or just one?

 

[Utradeshow]

Why do you have two numbers (E8 and E11)?
What do they represent?
Are you applying the tax to both numbers combined, or just one?

E8 + E11 are two taxable items, It would be the SUM of the 2 together

 

[joeu2004]

=50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*((E8+E11)-5000))
or
=50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*(E8+E11)-300))

The two formulas are algebraically equivalent.

You might want to explicitly round. But it's not clear to what precision: zero (integer); or 2 decimal places?

For example:

=ROUND(50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*(E8+E11)-300)), 0)

 

[joeu2004]

The catch is I need it to retain the amount of the 1% for the first 5000 ($50) [....] Eg 5000 would result in $350

=50 + MIN(300,6%*(E8+E11)) + MAX(0, 6%*((E8+E11)-5000))

The formula does the calculation that you did in your example, to wit: $50 plus 6% of $5000.

But I never understood what you mean by ``I need it to retain the amount of the 1% for the first 5000 ($50)``. I don't understand the word "retain" in this context.

And on second thought, my interpretation and your calculation does not make sense as a tax.

What if the taxable amount (E8+E11) is less that $50? Surely, the tax cannot be greater than the amount to be taxed.

I suspect that you misunderstand the tax rules entirely, and the tax on $5000 should not be $350. But I can only speculate what the correct rules might be.

If you agree that you need help understanding the tax rules, please post them exactly as they are written (ideally, an online reference to them), not your interpretation.

 

[Utradeshow]

The formula does the calculation that you did in your example, to wit: $50 plus 6% of $5000.

But I never understood what you mean by ``I need it to retain the amount of the 1% for the first 5000 ($50)``. I don't understand the word "retain" in this context.

And on second thought, my interpretation and your calculation does not make sense as a tax.

What if the taxable amount (E8+E11) is less that $50? Surely, the tax cannot be greater than the amount to be taxed.

I suspect that you misunderstand the tax rules entirely, and the tax on $5000 should not be $350. But I can only speculate what the correct rules might be.

If you agree that you need help understanding the tax rules, please post them exactly as they are written (ideally, an online reference to them), not your interpretation.

Ok, this is how the tax rule is. Not really important the cell locations at this time.

There is a State Sales tax of 6% and a county tax of 1%. The 1% percent is charged only on the first $5000 any amount above 5000 is charged the 6% only. The issue is if make the formula >5000 charge 7% and over 5000 charge 6% you lose the $50 net from the 1% of the first 5000. It may be too complex for one formula?

 

[joeu2004]

No. I think you are just overthinking it. There are two separate taxes: 1% up to $50 for the county; and 6% for the state. So:

=MIN(50, (E8+E11)*1%) + (E8+E11)*6%

And again, you probably should explicitly round. At a minimum:

=ROUND(MIN(50, (E8+E11)*1%) + (E8+E11)*6%, 2)

 

[Utradeshow]

No. I think you are just overthinking it. There are two separate taxes: 1% up to $50 for the county; and 6% for the state. So:

=MIN(50, (E8+E11)*1%) + (E8+E11)*6%

And again, you probably should explicitly round. At a minimum:

=ROUND(MIN(50, (E8+E11)*1%) + (E8+E11)*6%, 2)

Joe, Thank you!!! That calculation does exactly what I need to do. Sorry I was not so clear trying to explain the function.