# NPV Formula to Cut Off Values after Economic Limit

#### OilEconomist

##### Active Member
Thanks in advance for any suggestions, and I will provide feedback on any proposed solutions.

How do I modify the following formula to cut the columns off after the economic limit and beyond (i.e. not include them in the NPV Formula).

= NPV( ( 1 + \$B\$1 )^(1/12)-1, \$L\$198:\$ATO\$198)

Cell A1 has the date of the economic limit which is obtained from row 198.
Cell B1 has the discount factor (10% = 0.10)
Cell L198 is the starting period with the monthly cash flows and has the corresponding date of 1/1/2019
Cell ATO198 is the ending period of the monthly cash flows and has the corresponding date of 12/1/2218.

I find where the cash flow first goes negative in row 198 and place the corresponding date in cell A1. How do I ensure the cash flows including that date and after do not get counted in the NPV formula. The date will change depending on my cash flows.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### joeu2004

##### Banned user
First you say that A1 is the date "obtained from row 198". Then you say that monthly cash flows are in L198:ATO198. I assume that the corresponding dates are in L197:ATO197. Then:

=NPV((1+B1)^(1/12)-1, A198:INDEX(A198:ATO198, MATCH(A1, A197:ATO197, 0)-1))

I assume that the formula in A1 is (normally-entered):

=INDEX(A197:W197, MATCH(TRUE, INDEX(A198:W198<0,1,0), 0))

You could avoid the use of A1, if you wish, with the following (normally-entered):

=NPV((1+B1)^(1/12)-1, A198:INDEX(A198:ATO198, MATCH(TRUE, INDEX(A198:ATO198<0,1,0), 0)-1))

Last edited:

#### joeu2004

##### Banned user
How do I modify the following formula [....] =NPV( ( 1 + \$B\$1 )^(1/12)-1, \$L\$198:\$ATO\$198)
First you say that A1 is the date "obtained from row 198". Then you say that monthly cash flows are in L198:ATO198. I assume that the corresponding dates are in L197:ATO197.

Sorry for all the typos. Geesh! What I meant is.... (normally-entered):

=NPV((1+B1)^(1/12)-1, L198:INDEX(L198:ATO198, MATCH(A1, L197:ATO197, 0)-1))

I assume that the formula in A1 is (normally-entered):

=INDEX(L197:ATO197, MATCH(TRUE, INDEX(L198:ATO198<0,1,0), 0))

You could avoid the use of A1, if you wish, with the following (normally-entered):

=NPV((1+B1)^(1/12)-1, L198:INDEX(L198:ATO198, MATCH(TRUE, INDEX(L198:ATO198<0,1,0), 0)-1))

#### OilEconomist

##### Active Member
Thanks for your response and apologies for the confusion. I tried to simplify some of it to prevent the aforementioned.

The dates are in row 3 understanding that they start in column L.

In row 199, I insert the cash flows from 198. Once the cash flows in row 198 go negative for 12 months, I terminate them.

I then get when they terminate and obtain the respective date from row 3 and it gets put into cell A1.

I will test these formulas out and let you know the outcome. I'm sure they will work. You have been a tremendous help.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

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