topic Re: Invalid argument to function finance IRR in SAS Enterprise Guide

Invalid argument to function finance IRR

naresh1 — Thu, 24 Jan 2019 23:48:32 GMT

Not getting result when I ran below code. The finance function results based on data. The function IRR is a data dependant.

Data _null_;

Val1=Finance ('IRR', -185955.43, 5415.43, 230.94, -446.73, -1729.16, -3527.82, -5805.91, -8699.89, -12770.92, -18919.66,-29905.89, -53642.71,32686.83,107.48, 107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48,107.48,

107.48,107.48,107.48,107.48,107.48);

Put val1;

Run;

Re: Invalid argument to function finance IRR

ChrisBrooks — Fri, 25 Jan 2019 00:30:01 GMT

Hi Naresh and welcome to the community.

I've done some experiments with your data and with the finance('IRR',...) function and think the error may be due to the large negative values you have early in your list. I suspect these are causing a very large negative rate of return which the function can't handle.

You can simulate this more simply with the following code

data _null_;
	val1=finance('IRR',-10,20,2000,30);
	put val1=;

run;

This returns a result but if you make the third value -2000 instead of 2000 you get the same error you are seeing.

You may want to refer this to SAS Support by raising a track which you can do here -> https://support.sas.com/ctx/supportform/createForm

They should be able to confirm if my suspicions are correct and possibly offer a workaround.

Re: Invalid argument to function finance IRR

Reeza — Fri, 25 Jan 2019 00:55:26 GMT

I definitely think this is a SAS Support question. It doesn't appear related to the data, in my tests, I could get about 3/4 lines of 107.48 list and then it dies. It seems to error out at a specific number of entries....no idea why. It also errors out in other ways, so it's definitely finicky and both the documentation and errors are unhelpful.

Re: Invalid argument to function finance IRR

ballardw — Fri, 25 Jan 2019 00:57:37 GMT

I suspect the combination of values you are using is limiting the function.

I created a little macro that inserted a value repeatedly and generated the same error with as few as 3 parameters:

Data _null_;
Val1=Finance ('IRR',-185955.43,-100,-100);
Put val1;
Run;

But changing the last -100 to 100 yields a result. So it is likely something in the algorithm.

Re: Invalid argument to function finance IRR

Reeza — Fri, 25 Jan 2019 02:31:53 GMT

It errors out here for me, after the 53 (I think) entry. I wonder if there's a limit to the number of parameters allowed, ie 52?

Data _null_;
    Val1=Finance ('IRR', -185955.43, 5415.43, 230.94, -446.73, -1729.16, -3527.82, 
        -5805.91, -8699.89, -12770.92, -18919.66, -29905.89, -53642.71, 32686.83, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48);
    Put val1;
Run;

Re: Invalid argument to function finance IRR

ChrisBrooks — Fri, 25 Jan 2019 09:50:46 GMT

That's possible although the documentation (such as it is) doesn't mention a limit.

I've done a Google search on the Excel version of IRR and found that it's implementation of IRR also has similar issues. My suggestion of a very large negative rate of return causes problems with Excel as does multiple switching from positive to negative values and too many iterations of the algorithm being required to generate a solution as the algorithm is itself internally iterative (this might tie in with your suggestion of their being too many parameters for the function to handle). Assuming that both SAS and Excel use the same algorithm this strengthens the view that we won't solve this here and SAS Support would be the best port of call for @naresh1 to pursue this

Re: Invalid argument to function finance IRR

Ksharp — Fri, 25 Jan 2019 10:03:37 GMT

Did you also try function IRR() ?

Re: Invalid argument to function finance IRR

ballardw — Fri, 25 Jan 2019 15:44:19 GMT

@Reeza wrote:

It errors out here for me, after the 53 (I think) entry. I wonder if there's a limit to the number of parameters allowed, ie 52?

Data _null_;
    Val1=Finance ('IRR', -185955.43, 5415.43, 230.94, -446.73, -1729.16, -3527.82, 
        -5805.91, -8699.89, -12770.92, -18919.66, -29905.89, -53642.71, 32686.83, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 107.48, 
        107.48, 107.48, 107.48, 107.48);
    Put val1;
Run;

@Reeza

The limit is apparently very data value dependent. The little macro I wrote actually ran with up to 1000 same, admittedly not very large, positive values given a starting value in the -18000 range.

Re: Invalid argument to function finance IRR

Reeza — Fri, 25 Jan 2019 15:53:18 GMT

I don't remember my math finance that well, but I don't recall this being an intensive calculation.