|
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
"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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
Progress Tax Calculator
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 |
| All times are GMT +1. The time now is 05:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com