Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Perhaps something like this:

With a value in A1

The tax rate for A1 is:
B1: =MIN(4%+CEILING(A1/20000,1)/100,10%)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Take a look at:

http://www.mcgimpsey.com/excel/variablerate.html

HTH

"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Try this...

=IF(B5100000, (B5-100000)*0.1+7000, IF(B580000,(B5-80000)*0.09+5200,
IF(B560000,(B5-60000)*0.08+3600, IF(B540000,(B5-40000)*0.07+2200,
IF(B520000,(B5-20000)*0.06+1000, B5*.05)))))

Anything over 100K, you are basically paying $7000 on the first 100K
and then 10% on the rest, Anything from 80K - 100K, you are paying
5200 on the first 80K and 9% on the remainder and so on...

HTH


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=553335

"Matt" wrote in message
...
Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate
tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative
number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a
cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
--
David Biddulph

Based on the McGimpsey formula:

=SUMPRODUCT(--(A1{0;20000;40000;60000;80000;100000}),
(A1-{0;20000;40000;60000;80000;100000}), {0.05;0.01;0.01;0.01;0.01;0.01})

HTH

"Bearacade" wrote:


Try this...

=IF(B5100000, (B5-100000)*0.1+7000, IF(B580000,(B5-80000)*0.09+5200,
IF(B560000,(B5-60000)*0.08+3600, IF(B540000,(B5-40000)*0.07+2200,
IF(B520000,(B5-20000)*0.06+1000, B5*.05)))))

Anything over 100K, you are basically paying $7000 on the first 100K
and then 10% on the rest, Anything from 80K - 100K, you are paying
5200 on the first 80K and 9% on the remainder and so on...

HTH


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=553335

After reading the other responses, I KNEW I must have mis-read the post.

Here's a better formula:

For a taxable value in B3

The tax calculation would be:
=SUMPRODUCT(IF((B3/10000-{0,2,4,6,8,10})0,((B3/10000-{0,2,4,6,8,10})*10000)*{5,1,1,1,1,1}/100,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps something like this:

With a value in A1

The tax rate for A1 is:
B1: =MIN(4%+CEILING(A1/20000,1)/100,10%)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Shorter formula:

=SUM(((B3/10000-{0,2,4,6,8,10})0)*(B3/10000-{0,2,4,6,8,10})*10000*({5,1,1,1,1,1}/100))
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

After reading the other responses, I KNEW I must have mis-read the post.

Here's a better formula:

For a taxable value in B3

The tax calculation would be:
=SUMPRODUCT(IF((B3/10000-{0,2,4,6,8,10})0,((B3/10000-{0,2,4,6,8,10})*10000)*{5,1,1,1,1,1}/100,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps something like this:

With a value in A1

The tax rate for A1 is:
B1: =MIN(4%+CEILING(A1/20000,1)/100,10%)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Hi Ron

Thank you for this, a very effective formula I must say!
Solves my problem perfectly

Thanks again

Matt



"Ron Coderre" wrote:

Shorter formula:

=SUM(((B3/10000-{0,2,4,6,8,10})0)*(B3/10000-{0,2,4,6,8,10})*10000*({5,1,1,1,1,1}/100))
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

After reading the other responses, I KNEW I must have mis-read the post.

Here's a better formula:

For a taxable value in B3

The tax calculation would be:
=SUMPRODUCT(IF((B3/10000-{0,2,4,6,8,10})0,((B3/10000-{0,2,4,6,8,10})*10000)*{5,1,1,1,1,1}/100,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps something like this:

With a value in A1

The tax rate for A1 is:
B1: =MIN(4%+CEILING(A1/20000,1)/100,10%)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you

Thanks David

Helped me crack this problem, though to make it work I had to use MIN and MAX
=(5%*MIN(H$5,20000)+6%*MIN(MAX(H$5-20000,0),20000)+7%*MIN(MAX(H$5-40000,0),20000)+8%*MIN(MAX(H$5-60000,0),20000)+9%*MIN(MAX(H$5-80000,0),20000)+10%*MAX(H$5-100000,0))


"David Biddulph" wrote:

"Matt" wrote in message
...
Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate
tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative
number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a
cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
--
David Biddulph

Thanks Toppers

This is a little easier to understand for SUMPRODUCT newby's like myself

Matt


"Toppers" wrote:

Based on the McGimpsey formula:

=SUMPRODUCT(--(A1{0;20000;40000;60000;80000;100000}),
(A1-{0;20000;40000;60000;80000;100000}), {0.05;0.01;0.01;0.01;0.01;0.01})

HTH

"Bearacade" wrote:


Try this...

=IF(B5100000, (B5-100000)*0.1+7000, IF(B580000,(B5-80000)*0.09+5200,
IF(B560000,(B5-60000)*0.08+3600, IF(B540000,(B5-40000)*0.07+2200,
IF(B520000,(B5-20000)*0.06+1000, B5*.05)))))

Anything over 100K, you are basically paying $7000 on the first 100K
and then 10% on the rest, Anything from 80K - 100K, you are paying
5200 on the first 80K and 9% on the remainder and so on...

HTH


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=553335

Matt wrote:
"David Biddulph" wrote:
Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+
1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
[....]
Helped me crack this problem, though to make it work I had to use MIN and MAX
=(5%*MIN(H$5,20000)+6%*MIN(MAX(H$5-20000,0),20000)+7%*MIN(MAX(H$5-40000,0),20000)+
8%*MIN(MAX(H$5-60000,0),20000)+9%*MIN(MAX(H$5-80000,0),20000)+10%*MAX(H$5-100000,0))

You overlooked the key to the simplicity of David's solution (which is
the one I always use myself): except for the first pct factor (5%),
you multiply MAX(...) by the __incremental__ pct factor. For example,
if the "marginal rates" were 5%, 6%, 8%, 11% and 15%, you would write
=5%*... + 1%*max(...) + 2%*max(...) + 3%*max(...) + 4%*max(...).
Returning to your example, consider the amount 30000. David's
expression would evaluate to:

=5%*30000 + 1%*max(30000-20000,0)
=5%*30000 + 1%*10000
=5%*20000 + 5%*10000 + 1%*10000
=5%*20000 + 6%*10000

That is the same as your more complicated expression, which would
evaluate to:

=5%*min(30000, 20000) + 6%*min(max(30000-20000,20000), 20000)
=5%*20000 + 6%*10000

Ah, I see

Thanks David

Matt


" wrote:

Matt wrote:
"David Biddulph" wrote:
Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+
1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
[....]
Helped me crack this problem, though to make it work I had to use MIN and MAX
=(5%*MIN(H$5,20000)+6%*MIN(MAX(H$5-20000,0),20000)+7%*MIN(MAX(H$5-40000,0),20000)+
8%*MIN(MAX(H$5-60000,0),20000)+9%*MIN(MAX(H$5-80000,0),20000)+10%*MAX(H$5-100000,0))

You overlooked the key to the simplicity of David's solution (which is
the one I always use myself): except for the first pct factor (5%),
you multiply MAX(...) by the __incremental__ pct factor. For example,
if the "marginal rates" were 5%, 6%, 8%, 11% and 15%, you would write
=5%*... + 1%*max(...) + 2%*max(...) + 3%*max(...) + 4%*max(...).
Returning to your example, consider the amount 30000. David's
expression would evaluate to:

=5%*30000 + 1%*max(30000-20000,0)
=5%*30000 + 1%*10000
=5%*20000 + 5%*10000 + 1%*10000
=5%*20000 + 6%*10000

That is the same as your more complicated expression, which would
evaluate to:

=5%*min(30000, 20000) + 6%*min(max(30000-20000,20000), 20000)
=5%*20000 + 6%*10000