PS: I forgot to mention....

1. It might also be helpful if you "show your work" for the manual calculation that you believe returns the correct negative IRR. That is, show the data (values and dates) and formula that you use.

2. In your examples, Excel XIRR might return negative IRRs, but they only

__appear__ to be zero due to cell formatting. For example, -0.001% appears to be 0.00% when the cell is formatted as Percentage with 2 decimal places.

To avoid msunderstandings, I suggest that you format the cell as Scientific with 14 decimal places.

Nevertheless, those negative value still might not be the negative IRRs that you expect, based on manual calculations.

3. As you might know, Excel XNPV always returns an Excel error when the discount value (first parameter) is negative. It is a defect, IMHO.

So use SUMPRODUCT to verify that the value returned by Excel XIRR causes the NPV to be "close" to zero

__relative to__ the magnitude of the cash flows. Note:It is usually prudent to do this check, because Excel XIRR occassionally returns a completely bogus numerical value instead an Excel error when Excel XIRR cannot find a value that causes the NPV to be "close" to zero within its implementation limitations.

Consider my example (again, dates are in the form m/d/yyyy):

Code:

=XIRR({-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})

returns about -92.6186835020781%. But is that a valid IRR?

If that formula is in A1, note that the following incorrectly return a #NUM error:

Code:

=XNPV(A1,{-10000,1000,2000,3000},{"1/1/2015","2/1/2015","3/1/2015","4/1/2015"})

But the following formula in B1 returns about -0.00013585, which is "relatively close" to zero:

Code:

=SUMPRODUCT({-10000,1000,2000,3000}/(1+A1)^(({"1/1/2015","2/1/2015","3/1/2015","4/1/2015"}-"1/1/2015")/365))

Aside.... If we change A1 to C1 in that formula, and we copy __the value__ of A1 into C1, Goal Seek can drive B1 down to about -1.818989E-12, truly close to zero, by setting the value in C1 to -92.6186840021697%. A1 and C1 differ only by about 0.0000005%.

## Like this thread? Share it with others