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

[OilEconomist]

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)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Well Name[/TD]
[TD]Res Cat[/TD]
[TD]XIRR[/TD]
[TD]1/1/2019[/TD]
[TD]2/1/2019[/TD]
[TD]3/1/2019[/TD]
[TD]4/1/2019[/TD]
[TD]5/1/2019[/TD]
[TD]6/1/2019[/TD]
[TD]7/1/2019[/TD]
[TD]8/1/2019[/TD]
[TD]9/1/2019[/TD]
[TD]10/1/2019[/TD]
[TD]11/1/2019[/TD]
[TD]12/1/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Well A[/TD]
[TD]PDP[/TD]
[TD]N/A[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Well B[/TD]
[TD]NEC[/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Well C[/TD]
[TD]PUD[/TD]
[TD]32%[/TD]
[TD][/TD]
[TD]-9.5[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Well D[/TD]
[TD]PRB[/TD]
[TD]85%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-8[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Well E[/TD]
[TD]POS[/TD]
[TD]19%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-2[/TD]
[TD]1[/TD]
[TD]0.65[/TD]
[TD]0.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Well F[/TD]
[TD]RES[/TD]
[TD]64%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-6.5[/TD]
[TD]3[/TD]
[TD]2.5[/TD]
[TD]1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Well G[/TD]
[TD]PUD[/TD]
[TD]55%[/TD]
[TD][/TD]
[TD][/TD]
[TD]-8[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1.5[/TD]
[TD]1.25[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

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.

 

[OilEconomist]

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!

 

[joeu2004]

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

 

[OilEconomist]

That also works and is even better so I can avoid the array!

 

[OilEconomist]

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

 

[joeu2004]

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:

=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:

=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))), ""))

 

[joeu2004]

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

 

[OilEconomist]

Thank you so much JoeU2004! That worked!