Solved

Calculating income tax in Excel

Posted on 2011-02-16
10
1,096 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
  • 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
 

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 500 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compile Error 7 40
Copy value from a certain cell 5 24
Easy Excel formula needed 4 26
Excel sheet question 12 22
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now