Reply to thread

Message: [QUOTE="joeu2004, post: 7413057"] Yes, in theory. But did you even bother to try them, especially the 2nd set (reverse order)? If it truly worked for you, what version of Excel are you using? I do not have access to Excel 2013 or any Mac Excel. But with Excel 2003, 2007 and 2010, XIRR for the 2nd data set returns about 2.980E-09, not about 14.49% that XIRR correctly returns for the 1st data set. At first, this did not surprise me given the formula for XIRR. As I noted, the annualized period for each cash flow is (d[j]-d[1])/365. Note the reference to d[1], the first date in the range. However, when I use Goal Seek to derive the internal rate of return in G4 that results in zero for the formula =SUMPRODUCT(A4:E4/(1+G4)^((A5:E5-A5)/365)), G4 is indeed about 14.49%. This is due to the symmetry of PV and FV calculations. That is, the SUMPRODUCT parameter becomes an array of appreciated values, not discounted values, to wit: =SUMPRODUCT({1000,3000,4000,5000,-10000}/(1+G4)^({0,-366,-731,-1096,-1461}/365) (In the 3rd case of mixed order, we are discounting and appreciating to some "central" point in time, namely the first date in the range.) Nevertheless, if Goal Seek can derive the internal rate of return for that summation, XIRR should be able to as well. Moreover, my own Newton-Raphson implementation of XIRR does work with the dates in reverse order, even when I do not sort the data first. So the Excel errors in some cases and the bogus result in other cases seems to be a defect in the implementation of XIRR. I can imagine that XIRR is fixed in some later revision of Excel. [/QUOTE]

Verification