Quarterly tax percentages

[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

[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

"viz" <[email protected]> wrote in message
news:BEFEDE49.2B58%[email protected]...
> 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
>

[Krishnakumar]

Hi,

See the attachment.

HTH

[Biff]

Hi!

Downloaded your sample file.

What am I missing here?

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

Is "PAYG" some type of accounting/financial calculation technique?

Biff

"Krishnakumar" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> See the attachment.
>
> HTH
>
>
> +-------------------------------------------------------------------+
> |Filename: QtrlyTax.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
> +-------------------------------------------------------------------+
>
> --
> Krishnakumar
>
>
> ------------------------------------------------------------------------
> Krishnakumar's Profile:
> http://www.excelforum.com/member.php...o&userid=20138
> View this thread: http://www.excelforum.com/showthread...hreadid=387715
>

[viz]

On 16/7/05 4:23 PM, in article [email protected], "Biff"
<[email protected]> wrote:

> 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
>

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

> "viz" <[email protected]> wrote in message
> news:BEFEDE49.2B58%[email protected]...
>> 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
>>
>
>

[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


On 16/7/05 5:06 PM, in article
[email protected], "Krishnakumar"
<[email protected]> wrote:

>
> Hi,
>
> See the attachment.
>
> HTH
>
>
> +-------------------------------------------------------------------+
> |Filename: QtrlyTax.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
> +-------------------------------------------------------------------+

[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

"viz" <[email protected]> wrote in message
news:BEFFE8ED.2D02%[email protected]...
> 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
>
>
> On 16/7/05 5:06 PM, in article
> [email protected], "Krishnakumar"
> <[email protected]> wrote:
>
>>
>> Hi,
>>
>> See the attachment.
>>
>> HTH
>>
>>
>> +-------------------------------------------------------------------+
>> |Filename: QtrlyTax.zip |
>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>> +-------------------------------------------------------------------+
>

[viz]

On 17/7/05 4:23 AM, in article [email protected], "Biff"
<[email protected]> wrote:

> Hi!
>
> Downloaded your sample file.
>
> What am I missing here?
>
> How does 11/30/2004 equate to the 2nd qtr?

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?

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

>
> Biff
>
> "Krishnakumar" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> Hi,
>>
>> See the attachment.
>>
>> HTH
>>
>>
>> +-------------------------------------------------------------------+
>> |Filename: QtrlyTax.zip |
>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>> +-------------------------------------------------------------------+
>>
>> --
>> Krishnakumar
>>
>>
>> ------------------------------------------------------------------------
>> Krishnakumar's Profile:
>> http://www.excelforum.com/member.php...o&userid=20138
>> View this thread: http://www.excelforum.com/showthread...hreadid=387715
>>
>
>

[Biff]

Ok, got it!

Nov 30 is in the 2nd qtr of the fiscal year!

Biff

"viz" <[email protected]> wrote in message
news:BEFFEF3A.2D9F%[email protected]...
>
>
>
> On 17/7/05 4:23 AM, in article [email protected],
> "Biff"
> <[email protected]> wrote:
>
>> Hi!
>>
>> Downloaded your sample file.
>>
>> What am I missing here?
>>
>> How does 11/30/2004 equate to the 2nd qtr?
>
> 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?
>
> 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
>
>>
>> Biff
>>
>> "Krishnakumar"
>> <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> Hi,
>>>
>>> See the attachment.
>>>
>>> HTH
>>>
>>>
>>> +-------------------------------------------------------------------+
>>> |Filename: QtrlyTax.zip |
>>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>>> +-------------------------------------------------------------------+
>>>
>>> --
>>> Krishnakumar
>>>
>>>
>>> ------------------------------------------------------------------------
>>> Krishnakumar's Profile:
>>> http://www.excelforum.com/member.php...o&userid=20138
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=387715
>>>
>>
>>
>

[Biff]

PS -

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

That's why I got different results!

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Ok, got it!
>
> Nov 30 is in the 2nd qtr of the fiscal year!
>
> Biff
>
> "viz" <[email protected]> wrote in message
> news:BEFFEF3A.2D9F%[email protected]...
>>
>>
>>
>> On 17/7/05 4:23 AM, in article [email protected],
>> "Biff"
>> <[email protected]> wrote:
>>
>>> Hi!
>>>
>>> Downloaded your sample file.
>>>
>>> What am I missing here?
>>>
>>> How does 11/30/2004 equate to the 2nd qtr?
>>
>> 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?
>>
>> 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
>>
>>>
>>> Biff
>>>
>>> "Krishnakumar"
>>> <[email protected]>
>>> wrote in message
>>> news:[email protected]...
>>>>
>>>> Hi,
>>>>
>>>> See the attachment.
>>>>
>>>> HTH
>>>>
>>>>
>>>> +-------------------------------------------------------------------+
>>>> |Filename: QtrlyTax.zip |
>>>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>>>> +-------------------------------------------------------------------+
>>>>
>>>> --
>>>> Krishnakumar
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>> Krishnakumar's Profile:
>>>> http://www.excelforum.com/member.php...o&userid=20138
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=387715
>>>>
>>>
>>>
>>
>
>

[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


On 17/7/05 11:19 AM, in article
BEFFEF3A.2D9F%[email protected], "viz"
<[email protected]> wrote:

>
>
>
> On 17/7/05 4:23 AM, in article [email protected], "Biff"
> <[email protected]> wrote:
>
>> Hi!
>>
>> Downloaded your sample file.
>>
>> What am I missing here?
>>
>> How does 11/30/2004 equate to the 2nd qtr?
>
> 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?
>
> 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
>
>>
>> Biff
>>
>> "Krishnakumar" <[email protected]>
>> wrote in message
>> news:[email protected]...
>>>
>>> Hi,
>>>
>>> See the attachment.
>>>
>>> HTH
>>>
>>>
>>> +-------------------------------------------------------------------+
>>> |Filename: QtrlyTax.zip |
>>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>>> +-------------------------------------------------------------------+
>>>
>>> --
>>> Krishnakumar
>>>
>>>
>>> ------------------------------------------------------------------------
>>> Krishnakumar's Profile:
>>> http://www.excelforum.com/member.php...o&userid=20138
>>> View this thread: http://www.excelforum.com/showthread...hreadid=387715
>>>
>>
>>
>

[viz]

On 17/7/05 11:43 AM, in article [email protected],
"Biff" <[email protected]> wrote:

> Ok, got it!
>
> Nov 30 is in the 2nd qtr of the fiscal year!
>
> Biff

LOL - my fault Biff. Forgot that we have a differing format...

/viz

>
> "viz" <[email protected]> wrote in message
> news:BEFFEF3A.2D9F%[email protected]...
>>
>>
>>
>> On 17/7/05 4:23 AM, in article [email protected],
>> "Biff"
>> <[email protected]> wrote:
>>
>>> Hi!
>>>
>>> Downloaded your sample file.
>>>
>>> What am I missing here?
>>>
>>> How does 11/30/2004 equate to the 2nd qtr?
>>
>> 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?
>>
>> 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
>>
>>>
>>> Biff
>>>
>>> "Krishnakumar"
>>> <[email protected]>
>>> wrote in message
>>> news:[email protected]...
>>>>
>>>> Hi,
>>>>
>>>> See the attachment.
>>>>
>>>> HTH
>>>>
>>>>
>>>> +-------------------------------------------------------------------+
>>>> |Filename: QtrlyTax.zip |
>>>> |Download: http://www.excelforum.com/attachment.php?postid=3607 |
>>>> +-------------------------------------------------------------------+
>>>>
>>>> --
>>>> Krishnakumar
>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>> Krishnakumar's Profile:
>>>> http://www.excelforum.com/member.php...o&userid=20138
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=387715
>>>>
>>>
>>>
>>
>
>

[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