Allocate an income payment over a date range (tax years)

[Stephen]

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

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.

 

[GS]

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

 

[joeu2004]

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 are:
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 C9>AF4.

 

[Stephen]

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 are:
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 C9>AF4.

joeu2004,

Thank you for a very comprehensive answer.

I'll try to apply it (and understand it too!)

 

[Stephen]

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 are:
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 C9>AF4.

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?

 

[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. [....]
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),ROUND($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]

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),ROUND($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.

 

[Stephen]

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),ROUND($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