NPV Formula to Cut Off Values after Economic Limit

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top