Different result of IRR and XIRR for same cash flow

Dana wrote:

I believe the "large" difference is in the equation for XIRR.  It uses a standard 365 for a year, even in a leap-year.  Some "Days Differences" will be "slightly" greater than 365 in leap-years.

In some examples, that might be true.  But I do not believe that is the case for Amir's second example.

In that example, all of the dates are (or should be) 365 days apart.  IMHO, A1 should be 7-Aug-15, not 6-Aug-15.  So the array expression ($A$1:$A$7-$A$1)/365 yields the array {0,1,2,3,4,5,6}, which are the same exponents that Excel IRR would use in its series sum (i=0,...,6).

[ERRATA] Dana was probably referring to the truly small difference between Excel IRR with a 20% "guess" and XIRR without a guess -- 48.6032% for Excel IRR, 48.4965% for XIRR.  Yes, that difference is simply due to the unequal first period because Amir wrote 6-Aug-15 in A1.  Excel IRR assumes equal periods; XIRR sees the difference.  I was referring to the truly large difference between Excel IRR and XIRR, both without guesses, resulting in -2.7946% for Excel IRR.  My point is:  if both truly used essentially the same Newton-Raphson algorithm, their results should be the same when periods are truly equal (substituting 7-Aug-15 in A1) despite the different formulas -- Excel IRR using period number for exponents, XIRR using days/365 -- because days/365 is equal to the period number.

In any case, an Excel XIRR implementation the Newton-Raphson algorithm is demonstrated by the following table [1].

Note that it finds the IRR at about -2.7946%, the one found by Excel IRR.

The fact that Excel XIRR results sometimes do not match an independent Newton-Raphson implementation suggests to me that the Excel XIRR implementation is different in some way.  Either it fails to start with the "guess" (10%), notwithstanding documentation; or it is not a N-R algorithm [2].

I have long suspected the latter because https://support2.microsoft.com/kb/214105 says:  "The XIRR() function sets bounds on the discount rate above and below the correct rate by doubling guesses in each direction".

Note that the N-R algorithm does not rely on upper and lower bounds.  The description sounds more like the secant method.

(But IIRC, I implemented the secant method myself in the past, and the results were not close to the Excel XIRR results.  My recollection might be wrong.)

[1] I know Dana is familiar with the Newton-Raphson algorithm.  But for the benefit of others, this is a brief explanation.  Refer to http://en.wikipedia.org/wiki/Newton%27s\_method.

The algorithm is performed in sequential steps starting with Step #0 until either NPV is close to zero (it is exactly zero in step #12 above), or we exceed 20 steps (an Excel XIRR limitation).  Generally:

IRR[i] = IRR[i-1] + NPV/NPV'

where IRR[0] is 10%.  NPV' is the derivative of NPV.

Ostensibly, we could use Excel XNPV to calculate the NPV.  However, first, Excel XNPV does not permit negative IRR values, a design flaw IMHO; and second, we still need a formula for the derivative.

The SUMPRODUCT formulas for NPV in columns O and P are shown in column Q.  Refer to the Excel XIRR help page for details.

The derivative of a sum of terms is the sum of the derivatives of the terms.  Note the CF[i]/(1+r)^i = CF[i]*(1+r)^(-i).  And the derivative is -i*CF[i]*(1+r)^(-i-1) = -i*CF[i]/(1+r)^(i+1)

[2] [EDIT] Or perhaps the difference is simply that the Excel XIRR uses the difference quotient to estimate the derivative instead of calculating the exact derivative, as I do.

Amir wrote:

thanks for the response but if put regular intervals even then result is different.

please see the image..

First, understand that the IRR algorithm is very unstable mathematically.  See http://en.wikipedia.org/wiki/Internal\_rate\_of\_return.

Each example demonstrates another anomaly of the IRR algorithm and the Excel implementation in particular.

(Note:  If I write IRR and NPV, I am referring to the mathematical concepts.  I will try to write "Excel IRR" and Excel "NPV" when I am referring to the Excel implementation.)

I correctly explained the difference between Excel IRR and XIRR in your first example.  That was your mistake.

In your second example (I anticipate there will be others), the difference arises from the instability of the IRR algorithm, as well as apparent differences in the internal implementation of the Excel IRR and XIRR functions.

As the following table shows, the results from both Excel IRR and Excel XIRR are correct insofar as the NPV is nearly zero.

As the following table demonstrates, there are multiple IRRs, where the sign of the NPV changes.

(We must use SUMPRODUCT instead of Excel XNPV because the latter does not accept negative IRRs, a design flaw IMHO.)