Calulating PAYG automatically using Australian Tax Table

[confused111]

Hi all,
I need a formula to calculate Pay As You Go tax amount using an australian tax table. So If i simply enter in a gross salary, it will automatically calculate the tax amount for me.
Can anyone advise me how to do this using the IF function?
Taxable income Tax on this income
0 - $18,200 Nil
$18,201 - $37,000 19c for each $1 over $18,200
$37,001 - $80,000 $3,572 plus 32.5c for each $1 over $37,000
$80,001 - $180,000 $17,547 plus 37c for each $1 over $80,000
$180,001 and over $54,547 plus 45c for each $1 over $180,000

Thats the tax table - I'm becoming so frustrated because I just cant figure out how to calculate it!!!
Plz help!

[Mysore]

Hope this helps!

[joeu2004]

I need a formula to calculate Pay As You Go tax amount using an australian tax table. So If i simply enter in a gross salary, it will automatically calculate the tax amount for me. Can anyone advise me how to do this using the IF function?
Taxable income Tax on this income
0 - $18,200 Nil
$18,201 - $37,000 19c for each $1 over $18,200
$37,001 - $80,000 $3,572 plus 32.5c for each $1 over $37,000
$80,001 - $180,000 $17,547 plus 37c for each $1 over $80,000
$180,001 and over $54,547 plus 45c for each $1 over $180,000

Create the following table:

	A	B	C	D	E
1	Over....	Rate	Plus	Diff_Rate
2	0	0.0%	0	0.0%
3	18,200	19.0%	0	19.0%	=B3-B2
4	37,000	32.5%	3,572	13.5%	=B4-B3
5	80,000	37.0%	17,547	4.5%	=B5-B4
6	180,000	45.0%	54,547	8.0%	=B6-B5

The formulas in column D are shown in column E.

Then, if we enter the income in F8 (rounded down to the dollar), the PAYG can be calculated by one of the following formulas column G:

	F	G	H
7	Income	PAYG
8	50,493	7,957.23	=ROUND(SUMPRODUCT((F8>$A$2:$A$6)*(F8-$A$2:$A$6),$D$2:$D$6),2)
9		7,957.23	=ROUND((F8-VLOOKUP(F8,$A$2:$C$6,1))*VLOOKUP(F8,$A$2:$C$6,2) +VLOOKUP(F8,$A$2:$C$6,3),2)
10		7,957.23	=ROUND(IF(F8>$A$6,(F8-$A$6)*$B$6+$C$6, IF(F8>$A$5,(F8-$A$5)*$B$5+$C$5, IF(F8>$A$4,(F8-$A$4)*$B$4+$C$4, IF(F8>$A$3,(F8-$A$3)*$B$3,0)))),2)

The formulas in column G are shown in column H.

I would choose the SUMPRODUCT formula in G8. It relies on columns A, B and D (diff rate) in the table above. We do not need column C.

Alternatively, the VLOOKUP formula in G9 mimics the exact language of the PAYG table; so you might find that easier to understand. It relies on columns A, B and C in the table above. We do not need column D.

However, if your homework assignment requires that you use an IF formula, as you specified, use the formula in G10. It also relies only on columns A, B and C in the table above. We do not need column D.

[joeu2004]

Create the following table:
[....]
Then, if we enter the income in F8 (rounded down to the dollar), the PAYG can be calculated by one of the following formulas column G:

Refer to the attached Excel file.

(Cannot add to the previous posting by editing.)

[martindwilson]

this should do it
=SUMPRODUCT(--(A1>{18200;37000;80000;180000}), (A1-{18200;37000;80000;180000}), {0.19;0.135;0.045;0.08})
explanation here
http://www.mcgimpsey.com/excel/variablerate.html