# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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
4
Views
285
Replies
4
Views
322
Replies
0
Views
145
Replies
1
Views
642
Replies
2
Views
603

1,141,412
Messages
5,706,297
Members
421,439
Latest member
JordsdoExcel

### 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