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 effective solution.
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:
BRACKET 1:
$0 - $6000:Nil tax
BRACKET 2:
$6001 - $21600: 17c for each $1 over $6000
BRACKET 3:
$21601 - $58000: $2652 plus 30c for each $1 over $21600
BRACKET 4:
$58001 - $70000: $13572 plus 42c for each $1 over $58000
BRACKET 5:
Over $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 $500: $76.38
_______________
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)?
*sigh...
I would appreciate any help
Thank you all,
Mark
_________________________
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 effective solution.
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:
BRACKET 1:
$0 - $6000:Nil tax
BRACKET 2:
$6001 - $21600: 17c for each $1 over $6000
BRACKET 3:
$21601 - $58000: $2652 plus 30c for each $1 over $21600
BRACKET 4:
$58001 - $70000: $13572 plus 42c for each $1 over $58000
BRACKET 5:
Over $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 $500: $76.38
_______________
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)?
*sigh...
I would appreciate any help
Thank you all,
Mark
_________________________