modeling offset of losses against profits

[Bill]

Hi everyone,

Can anyone help with the 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

 

[David]

Hi Bill,
This may not be what you wnt but upi might look at the NPV function and you
can set the Interest Rate to 0.
Thanks,

 

[Tushar Mehta]

I'm not sure I understand the problem. If the cash flows are in B7:F7,
then in some cell, say B9 enter the formula =SUM($B$7:B7). Copy it to
C9:F9.

That is the basic building block. You can do all kinds of things with
it. For example, =IF(SUM($B$7:B7)>=0,"Yeh! Past breakeven",""). Or,
use conditional formatting to highlight the breakeven year.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions