Hi everyone,

Can anyone help wit hthe following please?

I have a cashflow where the first expressed in years where the first
few years are negative (aloss) and the next ,say, 10 or so are
positive.

I need to offset the losses against the profits as follows:


-100 -100 +50 +50 +100

in the flow above the -200 (-100+-100) is offset against the 50, 50,
and 100 hence makind the flow effectively zero.

This is easy to do manually but where the size of the positive flows
changes and the number of negative years change it becomes (i think )
almost impossible to automate.

I tried subtracting a running total but where the first positive year
was greater than the total negatives it failed!

Also, where there is . say, only one negative year the runnning total
fails!

Does anyone have any ideas. Perhaps involvong several rows worth of
built up functions?



Many thanks in advance


Bill

"Bill" wrote...
....
I have a cashflow where the first expressed in years where the first
few years are negative (aloss) and the next ,say, 10 or so are
positive.

I need to offset the losses against the profits as follows:


-100 -100 +50 +50 +100

in the flow above the -200 (-100+-100) is offset against the 50, 50,
and 100 hence makind the flow effectively zero.

This is easy to do manually but where the size of the positive flows
changes and the number of negative years change it becomes (i think )
almost impossible to automate.
....

For US personal income taxes, an individual may recognize no more than
$3,000 in net capital losses in any given year but must recognize in full
net capital gains in any year, including previous year capital loss
carryover. The formula to deal with that isn't all that complicated. If
year-by-year capital gain (loss) were in A1:A20, the taxable gain (loss) for
each year would be given by the formulas

B1:
=MAX(-3000,A1)

B2:
=MAX(-3000,SUM(A$1:A2)-SUM(B$1:B1))

with B2 filled down into B3:B20. Using simulated capital gains in A1:A20
given by the formula

=INT(-10000+40001*RAND())

one example of how this formula works appears as follows (underscores used
for spacing).

_22,190_ _22,190_
__8,991_ __8,991_
_(5,780) _(3,000)
__7,471_ __4,691_
_23,769_ _23,769_
_(1,204) _(1,204)
_26,248_ _26,248_
_21,619_ _21,619_
__9,637_ _9,637_
_18,174_ _18,174_
_18,154_ _18,154_
_14,458_ _14,458_
_(3,942) _(3,000)
_22,192_ _21,250_
_29,527_ _29,527_
_29,848_ _29,848_
_22,264_ _22,264_
_24,734_ _24,734_
____967_ ____967_
_(9,200) _(3,000)

If UK tax law doesn't allow negative taxable income in any year, so just
allows it to be carried forward as offsets to otherwise positive taxable
income in subsequent years, just replace the -3000 in my column B formulas
above with 0 (zero).

Harlan,

Many thanks.


You've saved ma a few grey hairs.


Regards



William



On Sun, 27 Mar 2005 19:07:48 -0800, "Harlan Grove"
wrote:

"Bill" wrote...
...
I have a cashflow where the first expressed in years where the first
few years are negative (aloss) and the next ,say, 10 or so are
positive.

I need to offset the losses against the profits as follows:


-100 -100 +50 +50 +100

in the flow above the -200 (-100+-100) is offset against the 50, 50,
and 100 hence makind the flow effectively zero.

This is easy to do manually but where the size of the positive flows
changes and the number of negative years change it becomes (i think )
almost impossible to automate.
...

For US personal income taxes, an individual may recognize no more than
$3,000 in net capital losses in any given year but must recognize in full
net capital gains in any year, including previous year capital loss
carryover. The formula to deal with that isn't all that complicated. If
year-by-year capital gain (loss) were in A1:A20, the taxable gain (loss) for
each year would be given by the formulas

B1:
=MAX(-3000,A1)

B2:
=MAX(-3000,SUM(A$1:A2)-SUM(B$1:B1))

with B2 filled down into B3:B20. Using simulated capital gains in A1:A20
given by the formula

=INT(-10000+40001*RAND())

one example of how this formula works appears as follows (underscores used
for spacing).

_22,190_ _22,190_
__8,991_ __8,991_
_(5,780) _(3,000)
__7,471_ __4,691_
_23,769_ _23,769_
_(1,204) _(1,204)
_26,248_ _26,248_
_21,619_ _21,619_
__9,637_ _9,637_
_18,174_ _18,174_
_18,154_ _18,154_
_14,458_ _14,458_
_(3,942) _(3,000)
_22,192_ _21,250_
_29,527_ _29,527_
_29,848_ _29,848_
_22,264_ _22,264_
_24,734_ _24,734_
____967_ ____967_
_(9,200) _(3,000)

If UK tax law doesn't allow negative taxable income in any year, so just
allows it to be carried forward as offsets to otherwise positive taxable
income in subsequent years, just replace the -3000 in my column B formulas
above with 0 (zero).