Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

XIRR - Numerical method did not converge....

Status
Not open for further replies.

CrystalUser2006

Technical User
Feb 15, 2006
1
US
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top