Calculating income tax in Excel

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!
mpaertAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
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
 
barry houdiniCommented:
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
 
cyberkiwiCommented:
You forgot to add earner's premium to the tax, since it is also deducted at source
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
cyberkiwiCommented:
Unbelievable Barry, answered brilliantly before I even attempted it.
0
 
mpaertAuthor Commented:
Thank you barry, but does this work in Google docs?
0
 
mpaertAuthor Commented:
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
 
barry houdiniCommented:
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
 
cyberkiwiCommented:
@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
 
barry houdiniCommented:
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
 
mpaertAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.