This app has to use Excel.I might consider writing my own XIRR if I could find a book, or published source, describing the numerical analysis details of the algorithm.

Excel may be using inferior algorithms, Calc gives the right answer for both XIRR and XNPV.

Excel may be using inferior algorithms, Calc gives the right answer for both XIRR and XNPV.

<t>I used XNPV to verify that the NPV of the stream is not 0 using the return given by XIRR.I then tried to use Goal Seek (never used it before).Given =XIRR(D3:D44, C3:C44), which returns -0.09833663106 in H2, =XNPV(H2,D3:D44, C3:C44) returns -2820.37.I then tried Goal Seek to determine the right va...

<t>QuoteOriginally posted by: gjlipmanIf this is a problem for you, why don't you code up your own IRR that uses best practice logic (which I admit I don't know what it is). Maybe even try and get hold of the Open Office algorithm. I mean, XIRR is just an addin function to Excel anyway - to use if a...

The following seems to work for positive and negative returns:=XIRR(F3:F45, E3:E45,SUM(F3:F44)/F45+1)But I do not think that it is right, e.g., if F45 were 0 or negative.Heck, I'm just gonna stick with the default and blame it on Microsoft.

<t>I just found a 2nd example of the problem.For both cases, Calc in Open Office 2.3 gives plausible results.In the example I posted, the cash flow is in a retirement accout that had a 0 balance on 26 Dec 2006, with subsequent contributions about every 2 weeks.THe other case is a sincgle mutual fund...

<t>QuoteOriginally posted by: gjlipmanYeah - there will be cases where Excel's default guess doesn't get you to the right answer - I guess it isn't optimised for cases where the IRR is negative. It may even be the case that it doesn't work properly - I notice that XNPV doesn't work in the case you s...

<t>I've used Excel's XIRR function in hundreds of formulae.I recently came across an instance in which Excel's default guess of .1 does not yield a plausible result.This is with Excel 2003.Opening the workbook with Open Office 2.3' Calc does yield a plausible result.If I explicitly give th function ...

<r>QuoteOriginally posted by: MMPQuoteOriginally posted by: kaikowIt seems that there were accuracy issues in version 1.1 of Calc.Any info on whether things have improved in more recent versions of Calc?How does Calc compare with Excel in numerical accuracy?I don't remember what versions I was using...

<t>QuoteOriginally posted by: DCFCExcel has three sets of calculation mode.Sheets are done in extended precision, ie 80 bit.VBA works in a mix of single and double precision (32 and 64 bit)Add-ins are usually 64 bit, but as C++ apps may choose to use extended precision.THe issue is the algorithms us...

It seems that there were accuracy issues in version 1.1 of Calc.Any info on whether things have improved in more recent versions of Calc?How does Calc compare with Excel in numerical accuracy?

<t>If one needs the searching and filtering, then ADO might be worthwhlile, but if only sorting is required, I suspect that it is relatvely easy to beat the pants off a built-in sorting routine, even using VB.I too felt that built-in sorting would be faster, ny I was awakened by the performance test...

Since you will not know in advance how many array elements you will need, you will have to use ReDim.ReDim is clearly explained in the VB 6 Help.If you are just doung ordinary mathematical calculations on elements of an array, using ADO, or whatever, database would just add needless overhead.

Sounds like you need to redesign the algorithm.Source of code for very efficient sort functions in VB.