Urgent: Irr help

[hardgrafting]

I am trying to calculate the IRR of the below cash flows. The result seems weird, I think it is because the first cash flow is not zero and the in the second set of cash flows, the third cash flows is negative.

Any ideas would be most appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]31/10/2018[/TD]
[TD]30/11/2018[/TD]
[TD]31/12/2018[/TD]
[TD]31/01/2019[/TD]
[TD]28/02/2019[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]-50[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]31/10/2018[/TD]
[TD]30/11/2018[/TD]
[TD]31/12/2018[/TD]
[TD]31/01/2019[/TD]
[TD]28/02/2019[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]50[/TD]
[TD]-10[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

The result seems "weird" how? Exactly what does the Excel function return? And exactly how are you calculating the IRR; with what formula?

That is basic information that you should always provide initially, especially when you have a usage question.

With the data posted, Excel IRR returns a #NUM error. Sometimes, that suggests that we need to provide a "guess". But I was unable to find a "guess" discount rate that would work. See the speculative explanation below.

The problem is not becuase the first cash flow is zero. In fact, that would cause inexplicable problems (read: defect) if you use Excel XIRR. Not so with Excel IRR.

Instead, the problem is that the NPV curve (for varying discount rates) does not seem to become zero for any discount rate. Consider discount rates as low as -99% through 100%; perhaps even greater.

I suspect that the last cash flow in each model is incorrect.

If we interpret positive numbers as deposits, presumably the last cash flow should reflect the ending balance as a negative value (withdrawal), not just the deposit.

If you provide more detail, I can be more specific.

 

[hardgrafting]

Thanks joeu2004. Please see an expanded explanation below, really appreciate your help.

I send revised cash flows:

[TABLE="width: 1096"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD="align: right"]30/11/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]31/01/2019[/TD]
[TD="align: right"]28/02/2019[/TD]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]30/04/2019[/TD]
[TD="align: right"]31/05/2019[/TD]
[TD="align: right"]30/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-1[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]Result 9.4%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1096"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD="align: right"]30/11/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]31/01/2019[/TD]
[TD="align: right"]28/02/2019[/TD]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]30/04/2019[/TD]
[TD="align: right"]31/05/2019[/TD]
[TD="align: right"]30/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]-100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]result 0.0%[/TD]
[/TR]
</tbody>[/TABLE]


You will see that both sets of cash flows are identical, aside from the first cash flow being -1 or 1 in 30/11/2018. It seems to me weird, that the result is so dissimilar.

What I am trying to get at, is that the XIRR calculation (which I have used to get both results), does not seem to work, when the first cash flow is positive? Is this correct?

The result seems "weird" how? Exactly what does the Excel function return? And exactly how are you calculating the IRR; with what formula?

That is basic information that you should always provide initially, especially when you have a usage question.

With the data posted, Excel IRR returns a #NUM error. Sometimes, that suggests that we need to provide a "guess". But I was unable to find a "guess" discount rate that would work. See the speculative explanation below.

The problem is not becuase the first cash flow is zero. In fact, that would cause inexplicable problems (read: defect) if you use Excel XIRR. Not so with Excel IRR.

Instead, the problem is that the NPV curve (for varying discount rates) does not seem to become zero for any discount rate. Consider discount rates as low as -99% through 100%; perhaps even greater.

I suspect that the last cash flow in each model is incorrect.

If we interpret positive numbers as deposits, presumably the last cash flow should reflect the ending balance as a negative value (withdrawal), not just the deposit.

If you provide more detail, I can be more specific.

 

[joeu2004]

First, if you format the second result as Scientific, you will see that what appears to be 0.0% is really about 2.98E-09.

In my experience, +/-2.98E-09 is another error state. Ostensibly, it should be treated like #NUM , namely: we should provide a "guess" discount rate.

Second, the mathematical IRR is a fragile "calculation" -- really, an iterative algorithm. It does not respond well to contrived cash flow models like yours.

The Excel XIRR implementation is even more fragile (flawed, IMHO) [1]; so is the Excel IRR implementation, albeit to a lesser extent, in my experience. My own XIRR implementation works well with your second cash flow model.

Since you have monthly cash flows, we can use a formula of the form =(1+IRR(A3:H3))^12-1 to estimate the annual compound IRR (what XIRR returns).

That returns 9.35545125625277% for the first example; 20.0545228418081% for the second example. And for each cash flow model, using those estimated annual IRRs with XNPV returns "close" to zero: less than 0.01 when rounded.

So you can see that the sign change of the first cash flow does indeed result in a significant difference in the mathematical IRR.

For the first example, =XIRR(A3:H3,A2:H2) returns 9.36799734830856%; and using that with XNPV returns nearly zero (about -2.27E-08).

But for the second example, =XIRR(A6:H6,A5:H5,20%) still returns 2.98E-09 or #NUM , even with a reasonable "guess".

I have not been able to find a "guess" that works. But again, using my own implementation, =myxirr(A6:H6,A5:H5) returns 20.0784186338246%; and XNPV returns nearly zero (6.66E-16). So the (X)IRR is indeed computable.

In situations where Excel XIRR cannot be made to work, and Excel IRR cannot be used or it fails to provide a suitable estimate, you might be able to use Goal Seek or Solver, varying the discount rate until Excel XNPV returns nearly zero.

However, Excel XNPV does not allow negative discount rates; a design flaw. So I would use a formula like the following to calcuate the NPV if the discount rate might be negative (e.g. using Goal Seek or Solver):

=SUMPRODUCT(A6:H6/(1+I6)^((A5:H5-A5)/365))

I hope that helps. I'm sorry there is no simple answer to your inquiry.



-----
[1] TMI.... I suspect that Excel XIRR (and Excel IRR) uses an approximated derivative, which is sensitive to a "step size". The arbitrary "step size" might cause the implementation to overshoot a solution, thereby finding an alternate IRR or none at all within the internal iteration limits. In contrast, my XIRR implementation uses the exact derivative, which is computable. Nevertheless, even my implementation might fail to find a solution. Moreover, there might be multiple mathematical IRRs, or none at all. So even a well-behaved implementation might fail to find a "good" solution.

 

[joeu2004]

Despite all of its flaws, Excel XIRR usually works well enough with a set of realistic cash flows. Your cash flows do not seem to be "realistic", especially if you can simply flip the sign of the first cash flow.

If your numbers are intended to represent a realistic cash, I think your model is flawed. I might be able to help you with that if you explain the numbers.

A typical explanation is something like this.... The first cash flow is an initial balance, deposit, investment or expense. The last cash flow is an ending balance or withdrawal of all funds. The middle cash flows are net deposits, investments or expenses minus (or plus) withdrawals. (If you use Excel XIRR, you can separate investments from withdrawals and use the same date.)

Note that the middle cash flows are not balances. The sign of the cash flows must be consistent. If you use minus for investments, the initial balance should be negative, and withdrawals and the final balance should be positive. Or the signs can be used vice versa.

 

[hardgrafting]

Thanks Joeu2004, ultimately, what I am trying to get to at this stage, is to have a dynamic model, which will provide an IRR, based on the below:

[TABLE="width: 1420"]
<colgroup><col><col span="9"><col span="3"></colgroup><tbody>[TR]
[TD="align: right"]31/10/2018[/TD]
[TD="align: right"]30/11/2018[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]31/01/2019[/TD]
[TD="align: right"]28/02/2019[/TD]
[TD="align: right"]31/03/2019[/TD]
[TD="align: right"]30/04/2019[/TD]
[TD="align: right"]31/05/2019[/TD]
[TD="align: right"]30/06/2019[/TD]
[TD="align: right"]31/07/2019[/TD]
[TD="align: right"]31/08/2019[/TD]
[TD="align: right"]30/09/2019[/TD]
[TD="align: right"]31/10/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] - 100.00[/TD]
[TD] 50[/TD]
[TD] 50[/TD]
[TD] 50[/TD]
[TD] 50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I want to be able to provide a negative cash flow at any point (in this example the first negative is 31/01/2019), however I want to flexibility to change the first negative cash flow to 31/03/2019 for example. The remaining positive cash flows will update automatically.

Therefore, I am looking for a solution, that will allow me to have one formula which will produce an IRR, by dynamically changing the timing and amount of the first negative cash flow, without needing the manually change the formula

 

[joeu2004]

Suppose the first possible date (31/10/2018) is in B1 and the last possible date is in Z1. (Your last date is in N1; but by using Z1, the formula accommodates some expansion, within reason.)

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

=XIRR(INDEX(B2:Z2,1,MATCH(TRUE,B2:Z2<0,0)):Z2, INDEX(B1:Z1,1,MATCH(TRUE,B2:Z2<0,0)):Z1)

Excel XIRR tolerates empty cells in the end (and middle) of the range, but not at the beginning. (Sigh.)