Anyone kind enough to tell me how top do this. Done it loads of times but now seem to have forgotten!
Tax Rate
0-145,000 = 0%
145,001-250,000 = 2%
250,001-325,000 = 5%
325,001-750,000 = 10%
Over 750,001 = 12%
sales price is in cell k3
thanks in advance
Are you still using XL2003? If not, please update your profile.
Here's one approach:
=IF(K3<=145000,0,IF(K3<=250000,(K3-145000)*2%,IF(K3<=325000,(K3-250000)*5%+(K3-145000)*2%,IF(K3<=750000,(K3-325000)*10%+(K3-250000)*5%+(K3-145000)*2%,(K3-750000)*12%+(K3-325000)*10%+(K3-250000)*5%+(K3-145000)*2%))))
but there are other ways that are not so tedious.
Hope this helps.
Pete
EDIT: No, I don't think that's right - scrub that.
Pete
Last edited by Pete_UK; 02-01-2017 at 06:31 AM.
thanks . looks good
its easy to forget this stuff even after 6 months !
This should work... =LOOKUP(K3,{0,145001,250001,325001,750001},{0,0.02,0.05,0.1,0.12}) and format as percent.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Did you not see my EDIT? - I don't think the formula is correct, but I have to go out now, so I'll pick this up later.
Pete
Correct . Both not working !
The tax bands are not mutually exclusive ie need to be added up ie if the sales price is over 250,001 ie 300,000
the tax would be zero on 0-145k and 5pc on 250k to 300k = 50k*5pc or 2,500
If you want it as a total I'd change mine then to ... =K3*LOOKUP(K3,{0,145001,250001,325001,750001},{0,0.02,0.05,0.1,0.12})
if I'm understanding your table correctly.
EDIT: I see what Pete gave you and what I'm giving you are two very different formulas that would return different results. In his he is saying that you only tax the amount over 145000 and less than 250000 (or the difference of 105000) at 2% and you leave the first amount of 145000 alone (no tax) and he is doing that in each series. I'm simply giving you a tax value on each level so that when you have less than 145001 it is 0 but if it is less than 250001 your tax value is 2%. So at 250001 it is taxing everything down to 0 at 2%. I hope that makes sense. I didn't see anything in your post saying you were carving out the values.
Last edited by Sam Capricci; 02-01-2017 at 07:38 AM.
sorry your understanding is incorrect
taxation bands mean you pay the tax rate applicable to EACH band not at a single rate . thats why they use bands ! otherwise they would just use tax rates over x.
so it needs to be calculated for each band then added up.
anyone know how to do this please ?
Yes, I'm back now - I understood the problem, but I just didn't have time to give you the correct solution. Try this instead:
=IF(K3<=145000,0,IF(K3<=250000,(K3-145000)*2%,IF(K3<=325000,(K3-250000)*5%+(250000-145000)*2%,IF(K3<=750000,(K3-325000)*10%+(325000-250000)*5%+(250000-145000)*2%,(K3-750000)*12%+(750000-325000)*10%+(325000-250000)*5%+(250000-145000)*2%))))
It might be better to put those values and corresponding tax rates in a small table somewhere and refer to the appropriate cell references, so that if the tax thresholds or tax rates change in the future then you only have to update the table rather than change the formula.
Hope this helps.
Pete
I used Match and Index formulas and came to the same results as Pete_UK
Kind regards,
Piet Bom
thanks
bang on
thanks also correct
If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.
Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks