Passing list of arguments to Finance(XIRR) function

[PAzevedo]

Hi there.

I've got a project where i need to calculate  the Internal Rate of Return for non periodic payments.

I'm using the XIRR Finance function to do it but i can't seem to find how to use it with variable input, or even if it's possible.

I've got a dataset with lots of rows and i need to calculate the XIRR for each row.

The problem is that every row as a different number of periods and the function doesn't seem to accept arguments like amt(1)-amt(n), neither i can use syntax like amt1-amtn as "n" is different in each row.

Consider the following example:

data a;

input amt1-amt5 per1-per5;

cards;

-100 101 102 103 . 1 2 3 4 .

-100 101 102 103 105 1 2 3 4 5

run;

For the first row i need to call the function for the first 4 amounts and periods:

IRR=FINANCE('XIRR',amt1,amt2,amt3,amt4,per1,per2,per3,per4);

For the second row i need to call the function for the first 5 amounts and periods:

IRR=FINANCE('XIRR',amt1,amt2,amt3,amt4,amt5,per1,per2,per3,per4,per5);

Now imagine this with a lot of input rows, each one with different amounts and periods.

At the moment i'm using a giant if trying the value for "n", generated by macro code based on the max periods i have in the input data which, as you can imagine, is heavy dutty!

Any ideas?

Regards,

PA.

[ballardw]

What is the range of your N?

It may be possible to test for the number of nonmissing arguments, for your example : test= 5 - nmiss(amt1-amt5)

and then use a select (test) to branch to the appropriate calculation.

Select(test);

     when (2) irr=finance(amt1,amt2,per1,per2);

     when (3) irr=finance(amt1,amt2,amt3,per1,per2,per3);

/*to your max n*/

    otherwise;

end;

admittedly if n gets large this is a tad clumsy but if you write the longest one first then copy paste in the editor and remove items doesn't take that long.

[PAzevedo]

The range on "N" is variable. It can be 1 as it can be 5000. N is calculated through two variables (date1 and date2) in my input data like N=date2-date1, which is the number of times that i have to calculate IRR for that row.

For each row the calculation goes like this:

1- Calculate "N" for that row

2- Calculate IRR for the row "N" times like:

N=1

IRR=FINANCE('XIRR',amt1,amt2,per1,per2);

N=2

IRR=FINANCE('XIRR',amt1,amt2,amt3,per1,per2,per3);

.

.

.

N=N

IRR=FINANCE('XIRR',amt1,amt2,...,amtN+1,per1,per2,...,perN+1);

If i understood your suggestion, what i have at the moment is like what you suggested.

I'm doing a first iteration of the input data to calculate max(N) so that in a second iteration i generate the select clause with macro coding. The problem here is the complexity of the iteration. I have: N rows * date2-date1 cycles * MAX(N) if tryes.

Has you can imagine, even with a small subset of data, it takes a while to compute this. The first two components of the formula are impossible to remove but the MAX(N) if tryes could be removed if there was a way to pass to the function a list of values, which i feel there isn't.

[data_null__]

Try this using the already mentioned conversion of missing to zersos.

data a;

   input amt1-amt5 per1-per5;

   cards;

-100 101 102 103 . 1 2 3 4 .

-100 101 102 103 105 1 2 3 4 5

run;

proc stdize reponly method=sum missing=0 out=a2;

   var _all_;

   run;

data a2;

   set a2;

   IRR=FINANCE('XIRR',of amt: per:);

   run;

proc print;

   run;

[Haikuo]

Hi,

First of all, I know close to nothing about this finance function, so I am not surprised that there are better solutions. It seems to me that:

1. Finance function does not take missing value as mean, max or other math functions, so when there is a missing value, the result will be missing.

2. Finance function does not take variable list very well, so you need to spell out all of the variable names explicitly.

3. In 'xirr' scenario, missing value can be replaced by zero????( not sure of this part, just a wild guess from comparing results).

So given above, you can try the following, first you need to replace all of the missings with zero, then just use one universal function.

data a;

input amt1-amt5 per1-per5;

array ir _numeric_;

do over ir;

ir+0;

end;

IRR=FINANCE('XIRR',amt1,amt2,amt3,amt4,amt5,per1,per2,per3,per4,per5);

cards;

-100 101 102 103 . 1 2 3 4 .

-100 101 102 103 105 1 2 3 4 5

run;

Good Luck,

Haikuo

[PAzevedo]

I'm not sure there is another way of implementing calculation that needs XIRR then implementing the algorithm behind it.

Since the original algorithm has a summatory i believe it wont much better or even better than using the finance function.

What you stated about the function is all true and as given me a real pain.

But i'm preatty sure your sugestion takes some complexity out of the algorithm i currently have.

To give an idea of what i'm currently facing here is a sample of the code (The code is simplified, there are some hash and key searches in the middle of this):

DO I=DATE1+1 TO DATE2;

   PERIOD+1;

   PER(PERIOD)=I;

   CASHFLOWS(PERIOD)=TEQUIV/100/365*1*-CASHFLOWS(1);

   AMT(PERIOD)=ABS(AMT(PERIOD-1))+CASHFLOWS(PERIOD);

      SELECT (PERIOD);

      %DO I=2 %TO &MAXP;

      WHEN (&I) TIR=FINANCE('XIRR',

           %DO J=1 %TO &I;

           AMT(&J),

           %END;

           %DO J=1 %TO &I;

           PER(&J),

           %END;

           .05)*100;

      %END;

       OTHERWISE;

       END;

END;

Each of the three arrays (cashflows, amt, per) are set to missing at the beggining of each iteration (call missing).

Setting them to zero at the beggining of each iteration, instead of missing, and calling the function with &maxp arguments instead of using the select clause might make this go faster.

I'll just have to evaluate if having zeros gives me the same result.

It was a nice tip, thk you.

[Astounding]

How about expanding the original suggestion of using zeros?

Use just one formula, for the maximum time period.  Run through a DO loop, each time setting one more value to 0 (starting at the end of the time periods and working back toward the beginning).  You'll be setting actual non-zero values to 0 along the way.  The question is, would the function generate the proper answer?

Good luck.

[Astounding]

Last idea for me ...

I imagine that the formula for an internal rate of return is not that complicated.  You already have arrays set up, with a DO loop, to populate CASHFLOWS and AMT for each period.  Why not just set up a few more arrays (and possibly a variable or two holding cumulative numbers across all prior periods), and let your current DO loop calculate the internal rate of return without using a function?

Good luck.

[Ksharp]

OR You can use lots of  Macro variable . But I am sure it isn't suited for large table.

data a;
input amt1-amt5 per1-per5;
cards;
-100 101 102 103 . 1 2 3 4 .
-100 101 102 103 105 1 2 3 4 5
;
run;
options missing=' ';
proc sql noprint;
 select count(*) into : n from a;
 select catx(",","IRR=FINANCE('XIRR'",amt1,amt2,amt3,amt4,amt5,per1,per2,per3,per4,per5)||");output;"
   into : list1 - : list%left(&n) 
    from a;
quit;

%macro a;
data want;
%do i=1 %to &n ;
 &&list&i
%end;
run;
%mend a;
%a 
 

Ksharp