XIRR Formula when not Cash Flow not Starting in the First Month

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
I’ve done a search for solution to my XIRR issue within this board and the web and have not found anything that’s quite the same to help me solve this issue.

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

To get the answers I have, I modified the XIRR function manually, but the number of wells in some cases can 400+. How to calculate XIRR when not starting in the first month? I adjusted the formulas in my table, but how can I do a VBA code or write a function to avoid doing this.

In Column B, I have the reserve category and when this is PDP (Proved Developed Producing) or NEC (Not Economic), the XIRR is “N/A”.

XIRR function for each row:
2: "N/A" since "PDP"
3: "N/A" since "NEC"
4: =XIRR(E4:O4, E$1:O$1, 0.5)
5. =XIRR(G5:O5, G$1:O$1, 0.5)
6. =XIRR(I6:O6, I$1:O$1, 0.5)
7. =XIRR(F7:O7, F$1:O$1, 0.2)
8. =XIRR(F8:O8, F$1:O$1, 0.2)


ABCDEFGHIJKLMNO
1Well NameRes CatXIRR1/1/20192/1/20193/1/20194/1/20195/1/20196/1/20197/1/20198/1/20199/1/201910/1/201911/1/201912/1/2019
2Well APDPN/A54321
3Well BNECN/A
4Well CPUD32%-9.54213
5Well DPRB85%-83222
6Well EPOS19%00000-210.650.4
7Well FRES64%00-6.532.51.5
8Well GPUD55%-8321.51.251

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into C2, then copy down:

=IF(OR(B2={"pdp","nec"}), "N/A", XIRR(INDEX(D2:O2,1,MATCH(TRUE,D2:O2<>0,0)):O2, INDEX($D$1:$O$1,1,MATCH(TRUE,D2:O2<>0,0)):$O$1))

Note that the third XIRR parameter ("guess") is omitted. It is not needed for your examples. There is no "guess" value that will work in all cases; and I do not know of any formula for predicting the required "guess" value, if the default does not work.
 
Last edited:
Upvote 0
joeu2004! You're the man! That worked 100% on the exact data I provided.

Now i just need to modify for my actual much larger data set which shouldn't be a problem!

This is awesome and I read tons of post and looked all over the web and nothing quite like this!
 
Upvote 0
You're welcome. If you want to avoid array-entering (I try to), use the following normally-entered formula instead (just press Enter as usual):

=IF(OR(B2={"pdp","nec"}), "N/A", XIRR(INDEX(D2:O2,1,MATCH(TRUE,INDEX(D2:O2<>0,0),0)):O2, INDEX($D$1:$O$1,1,MATCH(TRUE,INDEX(D2:O2<>0,0),0)):$O$1))
 
Upvote 0
Joe2004 thanks so much. The only issue is what if it needs to be halted based on an economic limit.

Basically say cell A1 has an entry for the economic limit of 6/1/2019, how do incorporate that into the formula where it does NOT continue to use the cells including and after 6/1/2019 (column I)?
 
Upvote 0
Arguably, this might be easier to do with OFFSET or INDIRECT. But I try to avoid so-called "volatile" functions like those; using too many of them can really degrade recalculation performance and editing. And in some designs, it only takes a few.

Ostensibly, if B11 contains the cut-off date, you might enter:

Rich (BB code):
=IF(OR(B4={"pdp","nec"}), "N/A",
XIRR(INDEX(D4:O4, 1, MATCH(TRUE, INDEX(D4:O4<>0,0),0)):INDEX(D4:O4, 1, COUNTIF($D$1:$O$1, "<=" & $B$11)),
INDEX($D$1:$O$1, 1, MATCH(TRUE,INDEX(D4:O4<>0,0),0)):INDEX($D$1:$O$1, 1, COUNTIF($D$1:$O$1, "<=" & $B$11))))

But with 6/1/2019 in B11, that correctly produces a #N/A error because it translates into XIRR(I6,I1), which is not a valid cash flow model.

Moreover, beware that XIRR might correctly be unable to find an IRR, even with a "guess", because some cash flows models are "truncated" arbitrarily. And the "guess" cannot be automated, AFAIK.

At the very least, it would be prudent to use IFERROR around the XIRR function, to wit:

Rich (BB code):
=IF(OR(B4={"pdp","nec"}), "N/A",
IFERROR(XIRR(INDEX(D4:O4, 1, MATCH(TRUE, INDEX(D4:O4<>0,0),0)):INDEX(D4:O4, 1, COUNTIF($D$1:$O$1, "<=" & $B$11)),
INDEX($D$1:$O$1, 1, MATCH(TRUE,INDEX(D4:O4<>0,0),0)):INDEX($D$1:$O$1, 1, COUNTIF($D$1:$O$1, "<=" & $B$11))), ""))
 
Upvote 0
Rich (BB code):
=IF(OR(B4={"pdp","nec"}), "N/A",
IFERROR(XIRR(INDEX(D4:O4, 1, MATCH(TRUE, INDEX(D4:O4<>0,0),0)):INDEX(D4:O4, 1, COUNTIF($D$1:$O$1, "<=" & $B$11)),
INDEX($D$1:$O$1, 1, MATCH(TRUE,INDEX(D4:O4<>0,0),0)):INDEX($D$1:$O$1, 1, COUNTIF($D$1:$O$1, "<=" & $B$11))), ""))

Since the dates in row 1 are probably in order, we can replace COUNTIF($D$1:$O$1,"<=" & $B$11) with MATCH($B$11, $D$1:$O$1).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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