NPV Formula to Cut Off Values after Economic Limit

[OilEconomist]

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.

 

[joeu2004]

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

 

[joeu2004]

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]

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.