XIRR

[satinsilhouette]

Can someone explain better than the help section, how XIRR is used?

If you have these figures with these dates:

Group ABC:

-10000 1/4/06
-10000 2/4/06
10000 1/5/05
30000 12/5/04


XIRR(values, dates)

The report is grouped and the data above is an example of one group.

Thanks so much!
satinsilhouette

 

[lbass]

I think the Help section is pretty clear. What questions do you still have?

-LB

 

[satinsilhouette]

For some reason it is not working? I guess you can manually enter the array values as in the example, but I don't think that is realistic since this is being read from a database over a period of time, many quarters, so then how does one populate the XIRR function with an array?

Maybe that is why people are confused by the help section.





Thanks so much!
satinsilhouette

 

[lbass]

You could set up the values and dates as arrays to then reference in the xirr formula. I tested this, but the problem was I kept getting the error message that the numerical values did not converge, and to try a different "guess" value. I couldn't get it to work whatever value I tried. Sorry.

-LB

 

[satinsilhouette]

I don't think you need a guess value. How did you do the array, that is what I am struggling with now.

Thanks so much!
satinsilhouette

 

[chelseatech]

I suspect you would need to build the array with a formula and a Global StringVar Array.

The confusion probabaly comes from the function needing an array, but the formula using it can only apply to one record.

Interesting point which I'll cover in our next newsletter. We convered the IRR and XIRR functions some time ago.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[satinsilhouette]

Hmm, I can get the arrays to populate, supposedly this should work like excel. But in excel I can tell it to look at a range of values A1:A3 for example.

So here is what I have so far, working off of an excel spreadsheet F1 is the customer name, F2 is the date field, F3 is the currency field. Group on the customer name. Both of these formulas builds the array, now how to add to the XIRR formula. If Excel looks at a range, then it is summing that range, right?

whileprintingrecords;
DateTimeVar Array DateApplied;
Redim Preserve DateApplied[UBound(DateApplied) + 1];
DateApplied[UBound(DateApplied)] := {Sheet1_.F2};

WhilePrintingRecords;
CurrencyVar Array Names;
Redim Preserve Names[UBound(Names) + 1];
Names[UBound(Names)] := {Sheet1_.F3};

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Okay so now how Lbass how did you fill the XIRR piece with the arrays?

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Let me add to this:

I have built a formula to populate an array for both a date field and a currency field.

In Excel if you have 5 records from a1:b5 then XIRR looks like this:

XIRR(A1:A5, B1:B5)

Here is a version that works in Crystal that would require manual entry of new quarters numbers and dates-- and if you plug this into Crystal it works:

(XIRR([1000000,-100000,-100000,-100000,-100000,10277.49,-100000], [DateValue(1999,2,1),DateValue(1999,3,1),DateValue(1999,6,1), DateValue(1999,12,1),DateValue(2000,3,1),DateValue(2000,6,1),DateValue(2000,9,1)]))*.100

So you have a range for the currency and a range for the date fields. I need to simulate this in crystal. Crystal has an XIRR function, and with the arrays, I should be able to fill in the range, but am having difficulty getting it to work.

After building the arrays, I made another formula to combine the arrays in the XIRR formula.

Global currencyvar array Names;
Global datetimevar array dateapplied;
XIRR((Names),(dateapplied))

Checking this formula it comes back with no errors, but running the report brings back this error:

The 'values' of the XIRR formula must contain both negative and postive numbers. WHich is true - when you run an array, it should contain both the negatives and the positives, but that's the error that comes back.

Will continue to work on this.


Thanks so much!
satinsilhouette

 

[lbass]

Do you still need help with setting up the array? I think you should maybe convert the currency to a number in your original formula.

-LB

 

[satinsilhouette]

I need help setting up the array correctly to feed into the XIRR formula. I am not great with loops or do's and I think this is what the formula needs. Interesting problem, I haven't worked something this hard in a long long time.

Thanks so much!
satinsilhouette

 

[lbass]

//{@Reset} for the group header (assuming you are using xirr at a group level and that you are NOT using a repeated group header):
whileprintingrecords;
numbervar array x := 0;
datevar array y := date(0,0,0);
numbervar i := 0;
numbervar j := 0;

//{@accum} for the detail section:
whileprintingrecords;
numbervar array x;
datevar array y;
numbervar i := i + 1;
numbervar j := count({table.groupfield},{table.groupfield});

if i <= j then (
redim preserve x[j];
redim preserve y[j];
x := tonumber({table.currency});
y := datevalue({table.datetime})
);

//{@xirr calc} to be placed in the group footer:
whileprintingrecords;
numbervar array x;
datevar array y;
xirr(x,y)

The array works correctly, but I do get an error message:

"Numerical method did not converge; try another value for guess." Then when I try various guesses, none work. I read that at least one value must be negative within the array, and in one place I read that it must come first. It is also true that the first date must be earlier than dates in later records.

Perhaps you can get this to work. I gave up.

-LB

 

[satinsilhouette]

I see where you are getting that error, need to try to work around that. Yes you do need to have at least one positive and one negative number and all dates have to historically fall after the initial investment date.

Thanks! Will try this, I wasn't quite there.

Thanks so much!
satinsilhouette