Hello one and all!
First of all, forgive me if my explanations are extremely simplified. I am not assuming my
fellow Boarders here lack intelligence or something; I do audits on work processes in my daily job (i.e. how to make them
more efficient etc.), and I have learnt that pure clarity is the quickest and most effective way to get to an appropriate and
So, here tis:
I have what appears (at least to me as a newbie) to be a complex
problem in Excel.
I am doing a Timesheet which I hope will work out the Gross, the Tax rate, and the final 'Net'
for each pay week.
I would like to calculate the Tax for each working days earnings and have it displayed
onscreen, and then have it totaled for the whole pay week (which will also be displayed). But here in Australia it is not a
simple matter of getting a Tax 'percentage' and then timsing the days gross by that (i.e. day's gross x .3 [for 30% Tax]).
In the Land of Oz, we have tax 'brackets':
If you earn: You pay:
$0 - $6000:Nil tax
$6001 - $21600: 17c for each $1
$21601 - $58000: $2652 plus 30c for each $1 over $21600
$58001 - $70000: $13572 plus 42c for each $1 over $58000
$70000: $18612 plus 47c for each $1 over $70000
The thing to note is that the way the Tax system here works, is
that if you Gross $500 one week, then that amount is timsed by the 52 weeks of the year (i.e. which comes to $26000, placing
one in 'Bracket 3'), and you are then taxed as if *that* will be your earnings for the whole year. BUT! if in the next week I
gross $1200, then THAT amount is timsed by 52 weeks (i.e. $62400), and I now find myself in the higher tax bracket (i.e.
Bracket 4) being taxed (for THAT week) as if I'm on 62 Grand a year (I wish!)
Theoretically, we get any excess
back at the end of the Tax year... But I digress...
For example, here is the mathematical Tax formula for a gross
of $500 in one week, which puts me into bracket number 3:
($500 * 52 = $26000) ($26000 - $21600 = $4400) ($4400 *
.3 = $1320) ($1320 + $2652 = $3972) ($3972 / 52 = 76.384615384615384615384615384615)
Tax for this weeks Gross of
I'm assuming that for this to work effectively in Excel I will need to do a Vlookup for
starters... but I have one more dilemma:
Once again, I would like the Tax for each day of work to be displayed,
but I can't get the Tax for the weeks gross and simply divide it by 5 business working days to get the Tax for a particular
day, because sometimes I work 7.5 hours a day, and on others I will work up to 9 hours in a day.. and other days - for
example - I might be unwell and not show up at all (which is rare I admit)!
And this, to me (as a relative excel
'newbie') seems to be the hardest part of all to grasp...
How do I get the individual Tax for each working day to
display (as well as formulating and implementing all of the above)?
I would appreciate any
Thank you all,