I want a formula that will return the payable tax value when applied on a taxable income using a given tax rate chart below:
Assuming the taxable income is 34100
Below or equal 2100 at 0%
Next 2100 at 10%
Over 4200 at 25%
Please assist urgently.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Tax computation formula
0
Answers
0
Selected Answer
if your amount/ taxable income in in cell A1, the use the following formula: =IF(A1>42000,25%,IF(A1>=21000,10%,0%)) [if it is not in A1 - you will need to change the A1's in the formula to the cell you have your info in.
Formula repeated below:
=IF(A1>42000,25%,IF(A1>=21000,10%,0%))Discussion
Thanks queue for your response. However, the figures that your formula returned does not add up.
The tax computation is suppose to follow this order:
a. when the A1 is less than or equal to 2100, then the tax rate to be applied will be 0%.
b. when A1 is greater than 2100, then the next 2100 will attract a tax rate of 10% and then if the taxable income is above 4200, then any amount above 4200 will attract a tax rate of 25%.
Note, my manual workings of the taxable income of 36200 gave a result of 8210.
As; Ist 2100= 0% *2100=0
Next 2100= 10% * 2100= 210
Above 4200 = 25% (36200-4200)=8000
**210+8000=8210
The tax computation is suppose to follow this order:
a. when the A1 is less than or equal to 2100, then the tax rate to be applied will be 0%.
b. when A1 is greater than 2100, then the next 2100 will attract a tax rate of 10% and then if the taxable income is above 4200, then any amount above 4200 will attract a tax rate of 25%.
Note, my manual workings of the taxable income of 36200 gave a result of 8210.
As; Ist 2100= 0% *2100=0
Next 2100= 10% * 2100= 210
Above 4200 = 25% (36200-4200)=8000
**210+8000=8210
olekachim (rep: 6) Dec 17, '18 at 11:48 am
I didn't get that from your original question. Try this formula =IF(A1>4200,25%*(A1-4200)+2100*0.1,IF(A1>=2100,10%*2100,0%)). Now that I have a better understanding of your question - does this work? ?
queue (rep: 467) Dec 17, '18 at 12:55 pm
I didn't get that from your original question. Try this formula =IF(A1>4200,25%*(A1-4200)+2100*0.1,IF(A1>=2100,10%*2100,0%)). Now that I have a better understanding of your question - does this work? ?
queue (rep: 467) Dec 17, '18 at 2:22 pm
Thanks. Your answer gave me 98% of the clue i needed. I simply adjusted it slightly to this:
=IF(A1>4200,25%*(A1-4200)+2100*0.1,IF(A1>2100,(A1-2100)*10%,0%))
olekachim (rep: 6) Dec 17, '18 at 3:08 pm
0
Best practise is to have parameters on the worksheet, not in a formula. So, when parameters change you don't need to modify formulas - just enter the new parameters. Therefore
[B1] = Lower tax bracket start
[B2] = Lower tax rate (%)
[C1] = Upper tax bracket start
[C2] = Upper tax rate (%)
[A4] = Taxable amountAfter this preparation the formula below will work out the tax payable for any amount entered in column A. You can copy the formula down from its original position in row 4.
=(MAX(($A4-$C$1)*$C$2,0))+(MIN((MAX($A4-$B$1,0))*$B$2,$B$1*$B$2))Of course, you can place the parameters anywhere on your sheet and change the formula to point to the cells you prefer. That's a one-time change. Similarly, you can place the parameters in named ranges (cells) anywhere in your workbook and replace the sell references in the formula with the names you assign. Either way is good practise.
Less good, but more common, is to replace the cell references B1, B2, C1 and C2 in the formula with actual numbers. That will work, too, but it will cause you more work whenever the parameters change.
Discussion
Hi Olekachim,
You now have three answers to choose from and already "selected" an answer that doesn't work for you. As a guide to other readers of this forum please do not select an answer that doesn't solve your problem. Only you can award points, and you should do that diligently. Thank you.
You now have three answers to choose from and already "selected" an answer that doesn't work for you. As a guide to other readers of this forum please do not select an answer that doesn't solve your problem. Only you can award points, and you should do that diligently. Thank you.
Variatus (rep: 4889) Dec 20, '18 at 2:53 am
0
we can try the below formula also
=IF(AND(A1>0,A1<=2100),0%,IF(AND(A1>2100,A1<=4200),10%,IF(A1>4200,25%)))
Discussion
Your formula doesn't return the tax amount, only the percentage with which to calculate that amount. You may like to make a small amendment to your answer to include that. However, I think if you do that your formula will return the same result as Queue's which Olekachim rejected.
Variatus (rep: 4889) Dec 20, '18 at 2:51 am