Link to home
Get AccessLog in
Avatar of mpaert
mpaert

asked on

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!
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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
You forgot to add earner's premium to the tax, since it is also deducted at source
Unbelievable Barry, answered brilliantly before I even attempted it.
Avatar of mpaert
mpaert

ASKER

Thank you barry, but does this work in Google docs?
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of mpaert

ASKER

Barry, you're a genius. Could you throw me freebee? What would the formula be if A3 showed how many % A2 is from A1?
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
@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
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
Avatar of mpaert

ASKER

Thanks!