Help in calculating XIRR for entries from different columns

What do you think the {1,0} is doing?

Can you explain what you are attempting to do, as you formula syntax is VERY strange??

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into F3, then copy down through F19:

=XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3)

Note that the last cash flow in each series must be the net cash flow: additional investment (column C) minus ending balance (column E), not simply the ending balance (negated).

Originally Posted by rishiambekar

suggest what other possible combination of functions I could you to overcome this limitation

Really, none is guaranteed to avoid adding a "guess". And unfortunately, I don't know of any way to automate the determination of a "guess".

(Other than a customized VBA function, which I do not have time to develop. But keep in mind that for some cashflow series, there might be multiple IRRs or none at all. That's the mathematical nature of finding IRRs.)

However, Excel IRR seems to be a more stable implementation than Excel XIRR. And I had considered suggesting that you use Excel IRR anyway, since your cash flows are monthly.

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula into F3, then copy F3 and paste into F4:F19.

=(1 + IRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3)))^12 - 1

If you compare with the results from my previous XIRR formulas, you will see some difference -- as much as 0.25%. The difference arises because Excel IRR assumes equal periods, whereas XIRR uses the exact number of days
between cashflows.

But I want to reiterate: it is only a coincidence that Excel IRR does not need a "guess" when used with your example. It might need a "guess" with another example. It simply cannot be avoided.

PS.... An alternative is to calculate the so-called time-weighted return (TWR), aka time-weighted rate of return (TWRR). (IMHO, it is neither time-valued nor time-weighted.) However, that requires more information; or we can make some assumptions, for example: all deposits occur at the beginning of the month. Nonetheless, the TWR is always computable. And IMHO, it is a better reflection of the market rate of return. If you're curious, start by reading the wikipage.

Or apply a round to the final result:

=ROUND(XIRR(IF(ROW($C$2:C3)=ROW(C3), C3-E3, $C$2:C3), $B$2:B3),5)

here set to 5 dps. Adjust as desired/appropriate for the precision of your need.

suggest what other possible combination of functions I could you to overcome this limitation.

I don't have a new combination of functions -- rather a change of spreadsheet. Are you required to use Excel for this? I have noted in the past that some of the numerical instability that causes Excel's implementation of the XIRR() function to return 2E-9 or other error/nonsense value is unique to Excel. In other spreadsheets (Google sheets, LibreOffice Calc, etc.), the XIRR() implementation is more stable and will return correct answers when Excel's implementation fails.

The present case, for example, I opened the sample file in post #1 in Gnumeric, array entered Joeu2004's formula from post #3 (the one that did NOT include a "guess" parameter) into F2 with no edits, and copied down to F18. The results all seemed reasonable, non-zero, non-error results. If you are allowed to use a spreadsheet other than Excel for this, you might find better numeric stability with other spreadsheets.

Originally Posted by rishiambekar

Is this a realistic number -44.09%?

"Realistic"?! It is not a word that I would associate with any IRR. (wink)

Seriously, in some contexts, the IRR "makes sense" as an interest rate.

But mathematically, an IRR is simply a discount rate that causes the NPV to be (nearly) zero.

My Excel IRR formula returns -44.1961900237213% in F8, which displays -44.20% (not -44.09%) when formatted as Percentage with 2 decimal places.

(My Excel XIRR formula returns -44.0892794914544%, which displays as -44.09%.)

We can check the Excel IRR result by array-entering (press ctrl+shift+Enter instead of just Enter) the following formula, formatted as Scientific:

=NPV((1+F8)^(1/12)-1, IF(ROW($C$2:C8)=ROW(C8), C8-E8, $C$2:C8))

For me, that returns about 6.11E-11, which is indeed "nearly zero". Therefore, yes, -44.1961900237213% is "realistic" insofar as it is mathematically correct.

(We should be able to use Excel XNPV to check the Excel XIRR result, but there is a defect: XNPV does not permit negative discount rates. Therefore, in general, we must use a SUMPRODUCT or array-entered SUM formula to calculate the "XNPV". But that is beyond the scope here, since you have decided to use Excel IRR, I presume.)

-----

It is possible that there is more than one IRR. If there is, you might feel that one of the other IRRs is more "realistic".

Look at the "NPV curve" to determine if there might be multiple IRRs. Enter -99%, -90%, -80%, etc through 100% into K3:K23. Then array-enter the following formula into L3, formatted as Scientific, and copy L3 into L4:L23:

=NPV((1+K3)^(1/12)-1,IF(ROW($C$2:$C$8)=ROW($C$8), $C$8-$E$8, $C$2:$C$8))

(See the attached Excel file.)

An IRR might be between two discount rates where there is a sign change in the NPV.

I see a sign change only between -50% and -40%. Thus, there probably is indeed only one IRR; and -44.20% is "realistic" insofar as it is between -50% and -40%.

Arguably, we might extend the NPV curve beyond 100%. But in this case, we can infer that it will just increase monotonically and perhaps become asymptotic to some large positive value.

(There is little point in extending the NPV curve below -99%, since the discount rate must be less than -100%.)

Originally Posted by rishiambekar

By just putting "guess" it gets down to -10.03% [....] At all three places where there is a "guess", it returns to the same result i.e. 10.03%. Strange?

As you can see in the attached file, I do not get -10.03% in all 3 places (G8, G18 and G19).

The only way that I might see -10.03% in all 3 places is to select multiple cells (instead of just one cell) when I array-enter the formula.

(But I cannot do that by selecting just G8, G18 and G19, because they are not contiguous.)

It is important to select just one cell when we array-enter these formulas. Then copy the one cell and paste into the other cells.

If you still have a question about this, please attach a (new) Excel file that demonstrates the repeated -10.03%. (Please do not replace your previous attachment.) And explain what you typed (keystroke-by-keystroke) to get repeated -10.03%.