Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Quarterly tax percentages
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 |
#2
|
|||
|
|||
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.13 12,B2*0.181)) Copy down as needed. Biff "viz" wrote in message . .. 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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" wrote in message news:Krishnakumar.1s966a_1121501107.3099@excelforu m-nospam.com... 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 |
#6
|
|||
|
|||
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 , "Krishnakumar" wrote: Hi, See the attachment. HTH +-------------------------------------------------------------------+ |Filename: QtrlyTax.zip | |Download: http://www.excelforum.com/attachment.php?postid=3607 | +-------------------------------------------------------------------+ |
#7
|
|||
|
|||
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" wrote in message . .. 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 , "Krishnakumar" wrote: Hi, See the attachment. HTH +-------------------------------------------------------------------+ |Filename: QtrlyTax.zip | |Download: http://www.excelforum.com/attachment.php?postid=3607 | +-------------------------------------------------------------------+ |
#9
|
|||
|
|||
Ok, got it!
Nov 30 is in the 2nd qtr of the fiscal year! Biff "viz" wrote in message . .. On 17/7/05 4:23 AM, in article , "Biff" 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" wrote in message news:Krishnakumar.1s966a_1121501107.3099@excelforu m-nospam.com... 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 |
#10
|
|||
|
|||
PS -
Then don't use my formula, it's based on the calendar year! That's why I got different results! Biff "Biff" wrote in message ... Ok, got it! Nov 30 is in the 2nd qtr of the fiscal year! Biff "viz" wrote in message . .. On 17/7/05 4:23 AM, in article , "Biff" 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" wrote in message news:Krishnakumar.1s966a_1121501107.3099@excelforu m-nospam.com... 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 |
#11
|
|||
|
|||
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(B4 8=$H$27,B48<=$I$27),C48*$ J$27,IF(AND(B48=$H$28,B48<=$I$28),C48*$J$28,IF(AN D(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 , "viz" wrote: On 17/7/05 4:23 AM, in article , "Biff" 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" wrote in message news:Krishnakumar.1s966a_1121501107.3099@excelforu m-nospam.com... 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 |
#12
|
|||
|
|||
On 17/7/05 11:43 AM, in article , "Biff" 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" wrote in message . .. On 17/7/05 4:23 AM, in article , "Biff" 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" wrote in message news:Krishnakumar.1s966a_1121501107.3099@excelforu m-nospam.com... 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 |
#13
|
|||
|
|||
Hi, Your formula can be shortened like the following.. =CHOOSE(MONTH(B48),0.1312,0.1312,0.1312,0.1312,0.1 81,0.181,0.181,0.1274,0.1274,0.1274,0.1312,0.1312) *C48 HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=387715 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating percentages in pivot tables on subtotals | Excel Worksheet Functions | |||
Calculating Percentages | Excel Discussion (Misc queries) | |||
quarterly reports | Excel Discussion (Misc queries) | |||
quarterly reports | Excel Worksheet Functions | |||
Calculating Percentages with Variables | Excel Worksheet Functions |