Quarterly tax percentages

V

viz

Must be getting old - can't work this one out... :(

Want to calculate a weekly PAYG (pay as you go) tax, based on a variable
quarterly tax percentage...

1st Quarter PAYG tax percantage 12.74%
2nd Quarter PAYG tax percentage 13.12%
3rd Quarter PAYG tax percentage 13.12%
4th Quarter PAYG tax percentage 18.10%

Examples:

Pay Date Total Weekly Sales PAYG
30/11/2004 2,257.40 296.17
17/05/2005 2,574.58 466.00
07/06/2005 3,180.21 575.62

So when I enter the "Total Sales" figure, the formula matches the Pay Date
to the appropriate quarterly PAYG tax percentage, multiplies the Total Sales
by that percentage and puts it under PAYG.

I hope that this is clear enough - I used to be able to do this at the drop
of a hat, but now either the brain has slowed down or I have not had enough
practice (probably both!).

TIA

/viz
 
B

Biff

Hi!

I don't end up with the same PAYG results that you have posted:

Dates = column A
Sales = column B

=IF(MONTH(A2)<=3,B2*0.1274,IF(MONTH(A2)<=9,B2*0.1312,B2*0.181))

Copy down as needed.

Biff
 
V

viz

Hi!

I don't end up with the same PAYG results that you have posted:

Dates = column A
Sales = column B

=IF(MONTH(A2)<=3,B2*0.1274,IF(MONTH(A2)<=9,B2*0.1312,B2*0.181))

Copy down as needed.

Biff
Click to expand...

H Biff - thanks for that. The PAYG might have differed from mine maybe
because I accidentally left the error in the example. Silly me :)

However I should have mentioned that the quarterly PAYG rates are variable,
and I wanted the formula to point to a listing rather than have it nesting
within the formula. I might play with your idea a little to see if I can
modify I :)

/viz
 
V

viz

Hi,

Getting this message:

Invalid Attachment specified. If you followed a valid link, please notify
the webmaster

Any other searches are proving fruitless. However thanks for the
introduction to that site - I have now registered :)

/viz
 
B

Biff

Hi!

You have to download the file directly from that site. Goto that site, then
download.

The sample file has the same results as you posted but I don't understand
it. The formula is pretty straight forward but I don't get the logic.

How does 11/30/2004 equate to the 2nd qtr?

Biff
 
V

viz

Hi!

Downloaded your sample file.

What am I missing here?

How does 11/30/2004 equate to the 2nd qtr?
Click to expand...

30th November 2004 is in the second quarter of the financial year in
Australia - where I live :) Other countries have different financial/tax
years - and different date formats. Australia is dd/mm/yy
Is "PAYG" some type of accounting/financial calculation technique?
Click to expand...

It is the tax system for self employed people (contractors like myself)
where tax is Pay As You Go - tax is paid quarterly on earnings. In the old
days, we had to pay provisional tax - tax paid in advance each quarter.

HTH

/viz
 
B

Biff

PS -

Then don't use my formula, it's based on the calendar year!

That's why I got different results!

Biff
 
V

viz

I got it! Lost a few neurons, but hey I've lost more to the demon alcohol...
;)

=IF(AND(B48>=$H$26,B48<=$I$26),C48*$J$26,IF(AND(B48>=$H$27,B48<=$I$27),C48*$
J$27,IF(AND(B48>=$H$28,B48<=$I$28),C48*$J$28,IF(AND(B48>=$H$29,B48<=$I$29),C
48*$J$29,""))))

Thanks for all those who helped :)

/viz
 
K

Krishnakumar

Hi,

Your formula can be shortened like the following..

=CHOOSE(MONTH(B48),0.1312,0.1312,0.1312,0.1312,0.181,0.181,0.181,0.1274,0.1274,0.1274,0.1312,0.1312)*C48

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to calc. tax of date & % 2
SUM and IF using two date ranges 2
Quarterly taxes query 2
How to store the calculated value into the original cell? 1
Quarterly Sales Tax Query 2
Calculating sales tax by purchase date 6
Grand Totals 2
Total Not Summing Correctly on Calculated Fields in Pivot Table 0

Top