BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
naresh1
Calcite | Level 5
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;
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisBrooks
Ammonite | Level 13

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

View solution in original post

8 REPLIES 8
ChrisBrooks
Ammonite | Level 13

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.

Reeza
Super User
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.
ballardw
Super User

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.

Reeza
Super User

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;

 

 

ChrisBrooks
Ammonite | Level 13

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

ballardw
Super User

@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.

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

Did you also try function IRR() ? 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

Creating Custom Steps in SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2994 views
  • 7 likes
  • 5 in conversation