Excel/TAX RATE BANDS

Advertisement


Question
QUESTION: I need a formula for the following

2015 RATES         Yearly
1.50%       UP_TO   12,012.00
3.50%   OVER  12,012     UP_TO   17,576.00
7.00%   OVER 17,576.00      UP_TO   70,044.00
8.00%   ALL  SALARY   OVER      70,044.00
I would be most gratefull if you could e-mail me a formula for same
The Tax due should be  9644.16 if ones salary was  140,000
regards  Alex
you may use = IF......

ANSWER: It's relatively simple, though I'd normally do this with a lookup table, which would make the job of updating it year on year easier

If I've followed this, for someone on 140,000 they would be taxed at 1.5% on the first 12012, then 3.5% on the next slice and so on?

Assuming my logic is right

=IF(Salary<=12012,Salary*1.5%,IF(Salary<=17576,((Salary-12012)*3.5%)+180.18,IF(Salary<=70044,((Salary-17576)*7%)+374.92,((Salary-70044)*8%)+4047.68)))

where Salary is replaced with the appropriate cell reference.

---------- FOLLOW-UP ----------

QUESTION: Thank you for the Formula but as I am paid Forthnightly can you re adjust the formula to take this into account.
Many  Thanks
Regards  Alex

ANSWER: I'm not sure how you want the formula adjusting - if you mean your salary is to be multiplied by 26 to get to the annual equivalent, then it would be salary*26 instead of Salary.  basic principle holds with the IF statement or lookup though

---------- FOLLOW-UP ----------

QUESTION: HI NO THE SALARY WOULD BE DIVIDED BY 26 CAN YOU AMEND THE FORMULA
MANY THANKS
ALEX

Answer
by Salary I assume you mean the tax band - but dividing that by 26 is the same as multiplying the amount you get paid BY 26 - however,

=IF(Salary<=12012/26,Salary*1.5%,IF(Salary<=17576/26,((Salary-(12012/26))*3.5%)+(180.18/26),IF(Salary<=70044/26,((Salary-(17576/26))*7%)+(374.92/26),((Salary-(70044/26))*8%)+(4047.68/26))))
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.