Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
I would like help allocating a Dollar value over different tax years.
To explain, my (Australian) tax years are 01/07 (1st July) - 30/06 (30th June) etc. If I receive an income contract payment that starts and finishes in the same tax year, say received 08/08/2010 (8th August 2010) and finishes 12/11/2010 (12th November 2010), ALLOCATE 100% to the tax year ended 30/06/2011 (as all the income should be apportioned to the one tax year. If I receive an income contract payment that starts in one tax year and finishes in the next tax year, say received 08/08/2010 (8th August 2010) and finishes 12/11/2011 (12th November 2011), ALLOCATE some to the tax year ended 30/07/2011 and the balance to the next tax year ending June 2012. In other words, look up the start date and then the end date, and if the end date is in the same tax year as the start date, 100% goes to this year. Otherwise, allocate only some to the current tax year and in the next cell, repeat... if the start date was in the previous tax year, only calculate what is due to the current tax year My variable a c9 = start date D10 = finish date AA9 = income received AE3 = 01/07/2010 (start date of a tax year) AE4 = 30/06/2011 (end of that tax year) AF3 = 01/07/2011 (start of next tax year) AF4 = 30/06/2012 (end of next tax year) AE10 = where I want to return the $$$ amount to be credited for the 2010-2011 tax year. AF10 = amount (if any) to be credited for the 2011-2012 tax year. I would prefer a IF (nested or not) statement, or SUMIF etc rather than VBA please. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
It appears that you are calculating your revenue on cash-based
accounting rather than accrued accounting. Which does your Revenue Agency expect you to report? I don't know of any Revenue Agency that permits using both methods for accounting. Most will consider your business as using the accrued method after the first accrued-type transaction occurs. On the accrued concept: If you were awarded contracts of value or made other 'Sales' in any fiscal year then that value is considered revenue for that fiscal year whether you receive full payment or monthly installments, OR if you deliver the goods/services within that fiscal year. Same goes for expenses claimed, whether the goods/services were received/paid in the fiscal year or not. On this premise, the amount of revenue claimed must align with the amounts paid to you by those reporting their respective expenses claimed for your goods/services. On the cash-based concept: Everything is done by completed transaction. On this premise, received income and paid expenses must be within the fiscal year. Revenue will be determined by the total amounts of all payments to you that are dated within your fiscal year. Expenses will be determined by all payments made by you within the fiscal year. Otherwise, you may want to look at using the DATEDIF() or portion according to months or days within fiscal dates. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
"Stephen" wrote:
I would like help allocating a Dollar value over different tax years. To explain, my (Australian) tax years are 01/07 (1st July) - 30/06 (30th June) etc. [....] In other words, look up the start date and then the end date, and if the end date is in the same tax year as the start date, 100% goes to this year. Otherwise, allocate only some to the current tax year and in the next cell, repeat... if the start date was in the previous tax year, only calculate what is due to the current tax year My variable a c9 = start date D10 = finish date AA9 = income received AE3 = 01/07/2010 (start date of a tax year) AE4 = 30/06/2011 (end of that tax year) AF3 = 01/07/2011 (start of next tax year) AF4 = 30/06/2012 (end of next tax year) AE10 = where I want to return the $$$ amount to be credited for the 2010-2011 tax year. AF10 = amount (if any) to be credited for the 2011-2012 tax year. In AE10: =IF(COUNT(C9,D10,AA9,AE3:AF4)<7,"", ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE3<=C9,D10<=AF4),ROUND(AA9-AE10,2), ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2))) Explanation.... The COUNT test in AE10 merely ensures that all numbers are filled in. Caveat: I do not bother to ensure that C9<=D10. (I use formulas in AE4, AF3 and AF4 to ensure that AE3 to AF4 is a contiguous 2-year period.) In AE10, ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the first tax year (AE3 to AE4), rounded to the cent. In AF10, the test AND(AE3<=C9,D10<=AF4) and the calculation ROUND(AA9-AE10,2) ensures that AE10+AF10 = AA9 exactly when C9 and D10 are wholly contained within the 2 tax years. In other words, it avoids any round-off error. (The use of ROUND avoids infinitesimal differences that creep into calculations due to the way that Excel represents numbers and performs arithmetic.) Otherwise, ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the second tax year (AF3 to AF4). The use of MAX(0,...) covers the cases when the contract period C9 to D10 is entirely outside the 2 tax years; that is, D10<AE3 or C9AF4. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
On 26/04/2012 2:23 AM, joeu2004 wrote:
wrote: I would like help allocating a Dollar value over different tax years. To explain, my (Australian) tax years are 01/07 (1st July) - 30/06 (30th June) etc. [....] In other words, look up the start date and then the end date, and if the end date is in the same tax year as the start date, 100% goes to this year. Otherwise, allocate only some to the current tax year and in the next cell, repeat... if the start date was in the previous tax year, only calculate what is due to the current tax year My variable a c9 = start date D10 = finish date AA9 = income received AE3 = 01/07/2010 (start date of a tax year) AE4 = 30/06/2011 (end of that tax year) AF3 = 01/07/2011 (start of next tax year) AF4 = 30/06/2012 (end of next tax year) AE10 = where I want to return the $$$ amount to be credited for the 2010-2011 tax year. AF10 = amount (if any) to be credited for the 2011-2012 tax year. In AE10: =IF(COUNT(C9,D10,AA9,AE3:AF4)<7,"", ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE3<=C9,D10<=AF4),ROUND(AA9-AE10,2), ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2))) Explanation.... The COUNT test in AE10 merely ensures that all numbers are filled in. Caveat: I do not bother to ensure that C9<=D10. (I use formulas in AE4, AF3 and AF4 to ensure that AE3 to AF4 is a contiguous 2-year period.) In AE10, ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the first tax year (AE3 to AE4), rounded to the cent. In AF10, the test AND(AE3<=C9,D10<=AF4) and the calculation ROUND(AA9-AE10,2) ensures that AE10+AF10 = AA9 exactly when C9 and D10 are wholly contained within the 2 tax years. In other words, it avoids any round-off error. (The use of ROUND avoids infinitesimal differences that creep into calculations due to the way that Excel represents numbers and performs arithmetic.) Otherwise, ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the second tax year (AF3 to AF4). The use of MAX(0,...) covers the cases when the contract period C9 to D10 is entirely outside the 2 tax years; that is, D10<AE3 or C9AF4. joeu2004, Thank you for a very comprehensive answer. I'll try to apply it (and understand it too!) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
On 26/04/2012 6:30 PM, Stephen wrote:
On 26/04/2012 2:23 AM, joeu2004 wrote: wrote: I would like help allocating a Dollar value over different tax years. To explain, my (Australian) tax years are 01/07 (1st July) - 30/06 (30th June) etc. [....] In other words, look up the start date and then the end date, and if the end date is in the same tax year as the start date, 100% goes to this year. Otherwise, allocate only some to the current tax year and in the next cell, repeat... if the start date was in the previous tax year, only calculate what is due to the current tax year My variable a c9 = start date D10 = finish date AA9 = income received AE3 = 01/07/2010 (start date of a tax year) AE4 = 30/06/2011 (end of that tax year) AF3 = 01/07/2011 (start of next tax year) AF4 = 30/06/2012 (end of next tax year) AE10 = where I want to return the $$$ amount to be credited for the 2010-2011 tax year. AF10 = amount (if any) to be credited for the 2011-2012 tax year. In AE10: =IF(COUNT(C9,D10,AA9,AE3:AF4)<7,"", ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE3<=C9,D10<=AF4),ROUND(AA9-AE10,2), ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2))) Explanation.... The COUNT test in AE10 merely ensures that all numbers are filled in. Caveat: I do not bother to ensure that C9<=D10. (I use formulas in AE4, AF3 and AF4 to ensure that AE3 to AF4 is a contiguous 2-year period.) In AE10, ROUND(AA9*MAX(0,MIN(D10,AE4)-MAX(C9,AE3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the first tax year (AE3 to AE4), rounded to the cent. In AF10, the test AND(AE3<=C9,D10<=AF4) and the calculation ROUND(AA9-AE10,2) ensures that AE10+AF10 = AA9 exactly when C9 and D10 are wholly contained within the 2 tax years. In other words, it avoids any round-off error. (The use of ROUND avoids infinitesimal differences that creep into calculations due to the way that Excel represents numbers and performs arithmetic.) Otherwise, ROUND(AA9*MAX(0,MIN(D10,AF4)-MAX(C9,AF3)+1)/(D10-C9+1),2) prorates the amount of AA9 to the second tax year (AF3 to AF4). The use of MAX(0,...) covers the cases when the contract period C9 to D10 is entirely outside the 2 tax years; that is, D10<AE3 or C9AF4. joeu2004, Thank you for a very comprehensive answer. I'll try to apply it (and understand it too!) joeu2004, I made my query too complicated for me to understand, even though you worked it out. Could I try an easier test please? All I want to do now is count the number of days that fall inside one tax year. Here is my spreadsheet: For the year 01/07/2008 - 30/06/2009, if END date < 1st July of this year, IGNORE if START DATE = than 1st July, and END Date is =< 30th June, count number of days If END date 30th June of this year, only count # of days from START date till 30th June inclusive In this example, the START and END dates fall inside the 01/07/2008 - 30/06/2009 tax year and the answer in cell E8 is "2" If the END date was say 05/07/2009, I would expect the answer to be 362 (03/07/2008 START date till 30/06/2009). In the next cell along, when I copy the same formula from E8 to E9, the answer would be 5 (01/07/2009 till 05/07/2009 END DATE) tax year tax year tax year tax year start date 3/07/2008 1/07/2007 1/07/2008 1/07/2009 1/07/2010 end date 5/07/2008 30/06/2008 30/06/2009 30/06/2010 30/06/2011 days this tax year - 2 - - total holding period 2.00 - $450.00 - - cash received $450.00 Is that easier please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
"Stephen" wrote:
I made my query too complicated for me to understand, even though you worked it out. Could I try an easier test please? All I want to do now is count the number of days that fall inside one tax year. [....] If the END date was say 05/07/2009, I would expect the answer to be 362 (03/07/2008 START date till 30/06/2009). In the next cell along, when I copy the same formula from E8 to E9, the answer would be 5 (01/07/2009 till 05/07/2009 END DATE) I suspect your only difficulty with the original formula is the lack of judicious use of absolute references [1]. That would make it easy to copy the formulas across. Download the following example from http://www.box.com/s/ce7f818b372ee4ae734c. Unfortunately, this forum does not permit to show a screen shot of the example [2]. The following might not be formatted correctly. (Caveat: I use the date format m/d/yyyy; you use d/m/yyyy. The format does not matter since I use DATE and EDATE to express dates.) A B C D E F 1 tax years 2 start date 7/3/2008 7/1/2007 7/1/2008 7/1/2009 7/1/2010 3 end date 7/5/2009 6/30/2008 6/30/2009 6/30/2010 6/30/2011 4 total days 368 5 total amt $450.00 6 days per tax yr 0 363 5 0 7 prorated amt $0.00 $443.89 $6.11 $0.00 The formula in B4 is: =B3-B2+1 The formula in C6 is: =MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1) The formula in C7 is: =ROUND($B$5*MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1)/$B$4,2) Because you want to copy C7 across several tax years, it is easier not to bother with avoiding rounding errors, as I did before. It can be done; but it might require different formulas in some cells. C6 can be copied across into D6 through F6. C7 can be copied across into D7 through F7. Ostensibly, the number of days is endDate minus startDate plus 1. See the formula in B4. MIN($B$3,C$3) selects the end date within that tax year. It is the contract end date (B3) or the end of the tax year (C3), whichever is earlier. MAX($B$2,C$2) selects the start date within that tax year. It is the contract start date (B2) or the start of the tax year (C2), whichever is later. MAX(0,...) covers the case when the contract end date is before the start of the tax year or the contract start date is after the end of the tax year. In that case, "endDate minus startDate plus 1" would be negative. MAX(0,...) forces the negative result to be zero. ----- [1] The original formulas might be written as follows: In AE10: =IF(COUNT($C$9,$D$10,$AA$9,AE$3:AF$4)<7,"", ROUND($AA$9*MAX(0,MIN($D$10,AE$4)-MAX($C$9,AE$3)+1)/($D$10-$C$9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE$3<=$C$9,$D$10<=AF$4),ROUN D($AA$9-AE10,2), ROUND($AA$9*MAX(0,MIN($D$10,AF$4)-MAX($C$9,AF$3)+1)/($D$10-$C$9+1),2))) [2] For future questions, I suggest that you post in the Excel Answers Forum at http://answers.microsoft.com/en-us/office/forum/excel. The "rich text" interface (GUI) allows us to paste screen shots, which might help you to visualize what we are talking about. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
"Stephen" wrote:
I made my query too complicated for me to understand, even though you worked it out. Could I try an easier test please? All I want to do now is count the number of days that fall inside one tax year. [....] If the END date was say 05/07/2009, I would expect the answer to be 362 (03/07/2008 START date till 30/06/2009). In the next cell along, when I copy the same formula from E8 to E9, the answer would be 5 (01/07/2009 till 05/07/2009 END DATE) I suspect your only difficulty with the original formula is the lack of judicious use of absolute references [1]. That would make it easy to copy the formulas across. Download the following example from http://www.box.com/s/ce7f818b372ee4ae734c. Unfortunately, this forum does not permit to show a screen shot of the example [2]. The following might not be formatted correctly. (Caveat: I use the date format m/d/yyyy; you use d/m/yyyy. The format does not matter since I use DATE and EDATE to express dates.) A B C D E F 1 tax years 2 start date 7/3/2008 7/1/2007 7/1/2008 7/1/2009 7/1/2010 3 end date 7/5/2009 6/30/2008 6/30/2009 6/30/2010 6/30/2011 4 total days 368 5 total amt $450.00 6 days per tax yr 0 363 5 0 7 prorated amt $0.00 $443.89 $6.11 $0.00 The formula in B4 is: =B3-B2+1 The formula in C6 is: =MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1) The formula in C7 is: =ROUND($B$5*MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1)/$B$4,2) Because you want to copy C7 across several tax years, it is easier not to bother with avoiding rounding errors, as I did before. It can be done; but it might require different formulas in some cells. C6 can be copied across into D6 through F6. C7 can be copied across into D7 through F7. Ostensibly, the number of days is endDate minus startDate plus 1. See the formula in B4. MIN($B$3,C$3) selects the end date within that tax year. It is the contract end date (B3) or the end of the tax year (C3), whichever is earlier. MAX($B$2,C$2) selects the start date within that tax year. It is the contract start date (B2) or the start of the tax year (C2), whichever is later. MAX(0,...) covers the case when the contract end date is before the start of the tax year or the contract start date is after the end of the tax year. In that case, "endDate minus startDate plus 1" would be negative. MAX(0,...) forces the negative result to be zero. ----- [1] The original formulas might be written as follows: In AE10: =IF(COUNT($C$9,$D$10,$AA$9,AE$3:AF$4)<7,"", ROUND($AA$9*MAX(0,MIN($D$10,AE$4)-MAX($C$9,AE$3)+1)/($D$10-$C$9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE$3<=$C$9,$D$10<=AF$4),ROUN D($AA$9-AE10,2), ROUND($AA$9*MAX(0,MIN($D$10,AF$4)-MAX($C$9,AF$3)+1)/($D$10-$C$9+1),2))) [2] For future questions, I suggest that you post in the Excel Answers Forum at http://answers.microsoft.com/en-us/office/forum/excel. The "rich text" interface (GUI) allows us to paste screen shots, which might help you to visualize what we are talking about. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Allocate an income payment over a date range (tax years)
On 29/04/2012 5:56 PM, joeu2004 wrote:
"Stephen" wrote: I made my query too complicated for me to understand, even though you worked it out. Could I try an easier test please? All I want to do now is count the number of days that fall inside one tax year. [....] If the END date was say 05/07/2009, I would expect the answer to be 362 (03/07/2008 START date till 30/06/2009). In the next cell along, when I copy the same formula from E8 to E9, the answer would be 5 (01/07/2009 till 05/07/2009 END DATE) I suspect your only difficulty with the original formula is the lack of judicious use of absolute references [1]. That would make it easy to copy the formulas across. Download the following example from http://www.box.com/s/ce7f818b372ee4ae734c. Unfortunately, this forum does not permit to show a screen shot of the example [2]. The following might not be formatted correctly. (Caveat: I use the date format m/d/yyyy; you use d/m/yyyy. The format does not matter since I use DATE and EDATE to express dates.) A B C D E F 1 tax years 2 start date 7/3/2008 7/1/2007 7/1/2008 7/1/2009 7/1/2010 3 end date 7/5/2009 6/30/2008 6/30/2009 6/30/2010 6/30/2011 4 total days 368 5 total amt $450.00 6 days per tax yr 0 363 5 0 7 prorated amt $0.00 $443.89 $6.11 $0.00 The formula in B4 is: =B3-B2+1 The formula in C6 is: =MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1) The formula in C7 is: =ROUND($B$5*MAX(0,MIN($B$3,C$3)-MAX($B$2,C$2)+1)/$B$4,2) Because you want to copy C7 across several tax years, it is easier not to bother with avoiding rounding errors, as I did before. It can be done; but it might require different formulas in some cells. C6 can be copied across into D6 through F6. C7 can be copied across into D7 through F7. Ostensibly, the number of days is endDate minus startDate plus 1. See the formula in B4. MIN($B$3,C$3) selects the end date within that tax year. It is the contract end date (B3) or the end of the tax year (C3), whichever is earlier. MAX($B$2,C$2) selects the start date within that tax year. It is the contract start date (B2) or the start of the tax year (C2), whichever is later. MAX(0,...) covers the case when the contract end date is before the start of the tax year or the contract start date is after the end of the tax year. In that case, "endDate minus startDate plus 1" would be negative. MAX(0,...) forces the negative result to be zero. ----- [1] The original formulas might be written as follows: In AE10: =IF(COUNT($C$9,$D$10,$AA$9,AE$3:AF$4)<7,"", ROUND($AA$9*MAX(0,MIN($D$10,AE$4)-MAX($C$9,AE$3)+1)/($D$10-$C$9+1),2)) In AF10: =IF(AE10="","",IF(AND(AE$3<=$C$9,$D$10<=AF$4),ROUN D($AA$9-AE10,2), ROUND($AA$9*MAX(0,MIN($D$10,AF$4)-MAX($C$9,AF$3)+1)/($D$10-$C$9+1),2))) [2] For future questions, I suggest that you post in the Excel Answers Forum at http://answers.microsoft.com/en-us/office/forum/excel. The "rich text" interface (GUI) allows us to paste screen shots, which might help you to visualize what we are talking about. joeu2004, You are a genius! The spreadsheet worked out exactly as desired, and when I change my START and END dates, it all works fine! Thanks for the explanation too - not sure I fully understand it all, but the formulas work, and that's the main thing! Thanks heaps, Stephen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the annual payment for a $4,000 loan, 3%, 3 years, | Excel Discussion (Misc queries) | |||
What is the formula payment for $48,800 for 30 years at 8% Interes | New Users to Excel | |||
Income / expense / net comparison over 4 years in one chart | Charts and Charting in Excel | |||
excel how do I calculate income indexed each year for 15 years | Excel Discussion (Misc queries) | |||
need an income statement forecasting worksheet for future years | Excel Programming |