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?