Formula to identify first year of positive cash flows

[CHBC1]

EDIT: I just figured it out. I don't know why I was having such a brain fart, but I just used a nested If statement to check whether the number was above 0. There are only 5 columns, so it wasn't bad at all. I guess I'm just curious now...are there any other cool excel functions to do this for me other than using a nested IF statement?

Thanks!

Initial post:
-------------------
Hello,

I know there must be a relatively straightforward situation, but I can't seem to wrap my mind around this. I have a spreadsheet that's calculating the cumulative net returns on investment of a project. I want to write a formula to automatically look at the data and tell me how many years it will take to turn positive. The data set looks something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year:[/TD]
[TD]CY16[/TD]
[TD]CY17[/TD]
[TD]CY18[/TD]
[TD]CY19[/TD]
[TD]CY20[/TD]
[/TR]
[TR]
[TD]Cumulative Net Returns[/TD]
[TD]-16[/TD]
[TD]-26[/TD]
[TD]-13[/TD]
[TD]9[/TD]
[TD]42[/TD]
[/TR]
</tbody>[/TABLE]

So the data is basically saying our payback period is 4 years (i.e. it will take 4 years for the additional revenue produced fromt his project to outweigh the initial implementation/ongoing costs).

What formula can I write to look at this data table and tell me how many years it takes to get to positive cash flows? For the sake of this example, let's say the "Year" cell is A1.

Thanks for your help!

 

[DRSteele]

This might be simpler than a nest of IFs.

	A	B	C	D	E	F	G
Year:	CY16	CY17	CY18	CY19	CY20
Cumulative Net Returns

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-16[/TD]
[TD="align: right"]-26[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]

</tbody>

Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]{=MATCH(TRUE,B2:F2>0,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[CHBC1]

This might be simpler than a nest of IFs.

	A	B	C	D	E	F	G
Year:	CY16	CY17	CY18	CY19	CY20
Cumulative Net Returns

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-16[/TD]
[TD="align: right"]-26[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]4[/TD]

</tbody>

Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]{=MATCH(TRUE,B2:F2>0,0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Very clever formula! That works perfectly. Thanks for the answer!