XIRR - Numerical method did not converge....

[CrystalUser2006]

Hi,

Does anyone know how to get around error message using the XIRR function when the guess is wrong? I have set up an array for the dates and values to calculate a return, but I keep running into the problem of "numerical method did not converge, please try another guess".

I am using an OLEDB connection against a SQL Server 2000 database. The report is written in Crystal XI.

Any help would be greatly appreciated!

Thank you.

 

[LTG3]

I'm suffering from the same problem.

My report would run fine except when the first record in the XIRR accumulation array returned a positive number. The XIRR formula requires the first number be a negative and at least one subsequent number to be positive. I created a workaround that checks to insure the first number is negative and if it not, then the XIRR formula is skipped. I get a zero return, but that's better than an error message.

Here's what I did:
On my accumulation array, on the first record, I have an if then statement that checks the value is below 0 and set a Boolean to true if it is. On all subsequent records, the Boolean=Boolean. Then in my XIRR formula, I have an if then statement that only proceeds if the Boolean is true. False returns a 0.

Insert the following formulas:
[on record accumulation array]
BooleanVar Test;
(OnfirstRecord)
If (Amount) <0 then Test:=true else Test:=false;
(On other records)
Test:=Test;
[On XIRR formula-before XIRR formula]
If Test=true then Xirr…
There is no need for a then statement, it will automatically return a 0.

Hope this helps.