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!

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!

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.

ASKER

Thank you barry, but does this work in Google docs?

ASKER CERTIFIED SOLUTION

membership

This content is only available to members.

To access this content, you must be a member of Experts Exchange.

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

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,1

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

@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

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

ASKER

Thanks!

=SUMPRODUCT((A1>{0,14000,4

The last array shows the differences between the percentages

See this method described here

see attached example

regards, barry

26826693.xls