Hi All,

I am a casual employee and trying to create a work time sheet that calculates my expected pay at the end of the week as I add and change shifts and hours.

I have got it to the point where it calculates my hours and then calculates things like weekend penalty rates etc and gives me my Gross pay, but I can't seem to work out how to create a formula that will calculate my tax and HECS debt.



The confusing thing about casual tax rates is that it doesn't appear to be a standard percentage of pay. Instead, the formula used by the tax department is y=xa-b where y is the amount of tax to be paid, x is the gross earnings for the week and a and b are values that change based on how much you have earned.

Weekly earnings a b
(x) less than $
395 .19 67.4635
493 .29 106.9673
711 .21 67.4642
1282 .3477 165.4431
1538 .3450 161.9815
3461 .39 231.2123
3461 and over .49 577.3662

So if I earned $684 for the week, the formula would look like y=(684*.21)-67.4642 making y=76.17

I've added the a and b values into my spreadsheet off to one side out of the way O9 - O14 and P9 - P14.

So basically, my Gross value is in K23. The formula I had come up with, but doesn't seem to be working is the following:

=IF(K23<355, 0), IF(AND(K23>355, K23<395), ((K23*O8)-P8)), IF(AND(K23>395, K23<493), ((K23*O9)-P9)), IF(AND(K23>493, K23<711), ((K23*O10)-P10)), IF(AND(K23>711, K23<1282), ((K23*O11)-P11)), IF(AND(K23>1282, K23<1538), ((K23*O12)-P12)), IF(AND(K23>1538, K23<3461), ((K23*O13)-P13)), IF(AND(K23>3461), ((K23*O14)-P14))

It could be as simple a thing as formatting, but I've been staring at it for about 3 hours now and zilch. I know this is a lot of info, so I apologise.

Any help or insight would be greatly appreciated.

Thanks,

Buggsy

So the formatting of the tabled info I tried to provide has failed, hopefully it makes sense still.

Perhaps post a sample sheet with your tax tables and an example to show desired results?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post