IRR Query

[hardgrafting]

Hi,

I have an IRR related query, which I am struggling to resolve, any advice would be most appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]30/04/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/05/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]30/06/2018[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]31/07/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]31/08/2018[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]31/10/2018[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]30/11/2018[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to calculate the IRR of the above cash flows, the first month 30/04/2018, will have 0 and it may be the case that the cash flows change, whereby the cash flow in 30/06/2018 will be zero and the first negative instance will be in 31/07/2018. Is there a modification to the XIRR formula that can be made, so the formula can begin from 30/04/2018 and end 30/11/2018 regardless of when the first zero starts?

Also, assume that the result of the XIRR formula, will be a monthly return, as opposed to annualised since we are dealing with months?

Thank you

 

[iggy_]

I think you need a negative value in the first period for the XIRR to work. Try putting a small negative value such as -.00000001 and seeing if that will work.

To get to a monthly IRR, just use this formula =(XIRR+1)^(1/12)-1

 

[joeu2004]

Mathematically, the IRR of the cash flows starting 30/4/2018 is the same as the IRR of the cash flows starting 30/6/2018 (first non-zero cash flow).

To demonstrate, set B1 to -1E-307, and calculate XIRR(B1:B8,A1:A8) and XIRR(B3:B8,A3:A8). The two results should be very close; in your example, they are identical.

So the easiest solution is to use only the data in A3:B8, if you can customize the XIRR formula. That is: XIRR(B3:B8,A3:A8).

Alternatively, array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=XIRR(IF(ROW(B1:B8)=ROW(B1),IF(B1=0,-1E-307,B1),B1:B8),A1:A8)

That says: if the first cash flow is zero, substitute an infinitesimally small value; otherwise, use the actual cash flow value.

However, if the cash flow on 30/6/2018 were 100 instead of -100, we should use 1E-307 instead of -1E-307. Off-hand, I do not know how to determine the sign of the first non-zero cash flow.

I use +/-1E-307 because it is close to the smallest decimal fraction that we can enter and it is easy to enter, and empirically I discovered that XIRR correctly treats it as non-zero. +/-1E-300 and +/-1E-100 would work equally well in nearly all case, and they might be easier to remember.

-----

In any case, the monthly IRR would be =(1 + XIRR(...))^(1/12) - 1 because XIRR always returns an annual IRR.

But in your case, XIRR might be more accurate that you truly need.

Since the cash flows are monthly (end of each month), IRR(B1:B8) might be a sufficiently accurate result.

Excel IRR returns a periodic IRR; monthly, in this case.

Also note that Excel IRR does the right thing when the first cash flow is zero.

Aside.... With your original data, XIRR(B1:B8,A1:B8) returns about 2.98E-09. That is a bogus numerical result. It seems to be an internal error state, similar in interpretation to when XIRR returns #NUM and #DIV/0.