?
Solved

Calculating income tax in Excel

Posted on 2011-02-16
10
Medium Priority
?
1,145 Views
Last Modified: 2012-05-11
Hi,

Cell A1 has a mothly salary figure before tax. Cell A2 should have the payable tax calculated that should be paid from that salary using the following rules:

Income per annum up to 14000 taxed at 10,5%
Income per annum between 14001 to 48000 17,5%
Income per annum between 48001 to 70000 30%
Income above 70001 taxed at 33%

The above percentages don't apply for the whole salary, say, if one is in the highest band, but instead, the first 14k are taxed at 10,5%, and the next 33999 are taxed at 17,5% etc until the entire salary of cell A1 is allocated across all tax bands.

See http://www.ird.govt.nz/how-to/taxrates-codes/itaxsalaryandwage-incometaxrates.html for more details

Thank you guys!
0
Comment
Question by:mpaert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34910792
Try this formula

=SUMPRODUCT((A1>{0,14000,48000,70000})+0,A1-{0,14000,48000,70000},{10.5,7,12.5,3}%)

The last array shows the differences between the percentages

See this method described here

see attached example

regards, barry
26826693.xls
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34910796
You forgot to add earner's premium to the tax, since it is also deducted at source
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34910814
Unbelievable Barry, answered brilliantly before I even attempted it.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mpaert
ID: 34910827
Thank you barry, but does this work in Google docs?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 34910927
I thought this was an Excel question!

I haven't checked but it probably doesn't in that form, you might need an array formula. Here's an alternative

=(A1*33-MIN(A1,70000)*3-MIN(A1,48000)*12.5-MIN(A1,14000)*7)/100

regards, barry
0
 

Author Comment

by:mpaert
ID: 34910971
Barry, you're a genius. Could you throw me freebee? What would the formula be if A3 showed how many % A2 is from A1?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34910999
Hello mpaert,

I'm not sure what you mean - can you give an example with expected result?

btw this version of the SUMPRODUCT formula (using SUM instead) should work in Googledocs

=ArrayFormula(SUM((A1>{0,14000,48000, 70000 })*(A1-{0,14000,48000,70000})*{10.5,7,12.5,3}))

regards, barry
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34912899
@mpaert just use the formula and divide it by A1 and format the column as percentage

@Barry what he means is if total tax take is 29K from 100K, it is a 29% overall take
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34920212
D'oh!

Thanks ck, I was a little slow there, somehow I wasn't equating A1 and A2 in mpaert's supplementary question to the original data and formula.....

regards, barry
0
 

Author Closing Comment

by:mpaert
ID: 34929438
Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question