[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calculating income tax in Excel

Posted on 2011-02-16
10
Medium Priority
?
1,148 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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 …

873 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