NPV Formula to Cut Off Values after Economic Limit

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 26, 2016
Messages
194
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top