# NPV Formula to Cut Off Values after Economic Limit

#### OilEconomist

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### joeu2004

##### Well-known Member
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:
• OilEconomist

#### joeu2004

##### Well-known Member
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

#### OilEconomist

##### Board Regular
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.

Replies
0
Views
294
Replies
3
Views
518
Replies
3
Views
131
Replies
2
Views
38
Replies
2
Views
42