Progress Tax Calculator

[Matt]

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$3>20000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$3>40000,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

[Ron Coderre]

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$3>20000,20000*0.05,B$3*0.05)
> Cell B6 =IF(B$3>40000,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

[Toppers]

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$3>20000,20000*0.05,B$3*0.05)
> Cell B6 =IF(B$3>40000,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

[Bearacade]

Try this...

=IF(B5>100000, (B5-100000)*0.1+7000, IF(B5>80000,(B5-80000)*0.09+5200, IF(B5>60000,(B5-60000)*0.08+3600, IF(B5>40000,(B5-40000)*0.07+2200, IF(B5>20000,(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

[David Biddulph]

"Matt" <[email protected]> wrote in message
news:[email protected]...
> 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$3>20000,20000*0.05,B$3*0.05)
> Cell B6 =IF(B$3>40000,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

[Toppers]

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(B5>100000, (B5-100000)*0.1+7000, IF(B5>80000,(B5-80000)*0.09+5200,
> IF(B5>60000,(B5-60000)*0.08+3600, IF(B5>40000,(B5-40000)*0.07+2200,
> IF(B5>20000,(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
>
>

[Ron Coderre]

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$3>20000,20000*0.05,B$3*0.05)
> > Cell B6 =IF(B$3>40000,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

[Ron Coderre]

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$3>20000,20000*0.05,B$3*0.05)
> > > Cell B6 =IF(B$3>40000,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

[Matt]

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$3>20000,20000*0.05,B$3*0.05)
> > > > Cell B6 =IF(B$3>40000,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

[Matt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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$3>20000,20000*0.05,B$3*0.05)
> > Cell B6 =IF(B$3>40000,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
>
>
>

[Matt]

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(B5>100000, (B5-100000)*0.1+7000, IF(B5>80000,(B5-80000)*0.09+5200,
> > IF(B5>60000,(B5-60000)*0.08+3600, IF(B5>40000,(B5-40000)*0.07+2200,
> > IF(B5>20000,(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
> >
> >

[[email protected]]

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

[Matt]

Ah, I see

Thanks David

Matt


"[email protected]" 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
>
>