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