Break-Even Point in Cashflow

Hi all,

I have a cashflow sheet and am looking to find a formula that returns the break-even point (that is, the month at which the cashflow no longer has negative entries and is over £0).

The following was useful in finding the FIRST month at which the cashflow becomes positive (>£0):

A1:Z1 = months
A2:Z2 = cashflow

Array formula:
=INDEX(A1:Z60,MATCH(TRUE,A2:Z2>=0,0))

However, in my particular case, there may be a month in which the cashflow turns positive and then is negative the month after (or later on), but eventually turns positive permanently (i.e. for the remainder of the project).

E.g. the cashflow might be -£100 in month 11, then £200 in month 12 (therefore the formula in the above link would return month 12 as the break-even point) but in my project, we might be back down to -£100 in month 14 and there may be this continuous fluctuation. From e.g. month 34, the cashflow might be £300 and remain positive from then on to the end (say, 60 months total).

How can I therefore write a formula to return month 34 in the above example as the break-even point i.e. the point from which the cashflow is >£0 and does not dip below £0 again?

Any help would be much appreciated.

Thanks

Hi,

Thank you for your help.

I understand the use of a helper line to return all the months that give a positive cumulative cashflow.

However, my aim is to find the last month at which the cashflow turns positive (from negative).

Your break-even formula with the MIN function is the first point of break-even, which is also useful to know, but I want to find a way to show the last break-even point (month 13 in your sample sheet).

The MAX function would return month 14, which is not what I want to achieve either.

Any suggestions?

Many thanks

Try this on. Hope it works for you.

Hi,

Perfect, that does exactly what I want!

Just wondering, does it make a difference to use a LOOKUP and LARGE vs just finding the MAX?

The way I adapted it was as in the attached

Actually it doesn't. I didn't catch it before. However, this version will.

Max or large should work for this.

Please remember to click on the star if you feel you've been helped.

Hope that takes care of everything.

Thanks a lot - added star and marked thread as solved

Glad to be of service!

Hi

My apologies my accounting background emphasised breakeven.

Cheers at least you have solved your problem.