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
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.
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.
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;
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
@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;
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.
Did you also try function IRR() ?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.