# Index (?) to get payback period?

#### Texas Longhorn

##### Active Member
Hi,

I have a workbook that models out production for an oil well. Column A is "Period", measured in days. Column F is "Cumulative NPV", measured in dollars. In cell N1, I have an initial well cost (\$800,000). In cell O1, I would like to return the period number from Column A at which the cumulative NPV in column F first exceeds the initial well cost in cell N1.

I've been trying combinations of INDEX and IF, but have not had any luck. Any help is much appreciated.

Thanks,

Bill

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Domenic

##### MrExcel MVP
Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A1:A100,MATCH(TRUE,SUBTOTAL(9,OFFSET(F1:F100,,,ROW(F1:F100)-ROW(F1)+1))>N1,0))

Hope this helps!

#### hatman

##### Well-known Member
=index(a:a,min(if(f1:f100>n1,row(f1:f100),65536))) Confirmed with Ctrl+Shift+Enter... adjust range as needed, but do not select all of colmn F

#### Domenic

##### MrExcel MVP
Oops, it looks like I missed the key part 'Column F is cummulative NPV'. My formula can be amended as follows...

=INDEX(A1:A100,MATCH(TRUE,F1:F100>N1,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

#### hatman

##### Well-known Member
Oops, it looks like I missed the key part 'Column F is cummulative NPV'. !

Whew

I thought I was the one who missed something.

#### Texas Longhorn

##### Active Member
Perfect. Thanks to both of you,

Bill

Replies
1
Views
84
Replies
1
Views
292
Replies
11
Views
335
Replies
0
Views
216
Replies
1
Views
354

1,190,580
Messages
5,981,766
Members
439,734
Latest member
hmopheim

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back