Using finance function

Reply
Contributor
Posts: 30

Using finance function

Hi, 

 

I need to use a finance function using variables and not numbers. 

 

The function is:

r = finance('xirr', v1, v2, d1, d2, 0.1);

 

data _null_;
   v1 = -10000; d1 = mdy(1,1,2008);
   v2 = 2750; d2 = mdy(3,1,2008);
   v3 = 4250; d3 = mdy(10,30,2008);
   v4 = 3250; d4 = mdy(2,15,2009);
   v5 = 2750; d5 = mdy(4,1,2009);
   r = finance('xirr', v1, v2, v3, v4, v5, d1, d2, d3, d4, d5, 0.1);
   put r = ;
run;

 

I wand to v1, v2, d1 and d2 be variables from a table. 

 

How can I do it?

 

Thanks and kind regards,

 

Maria

Super User
Posts: 10,527

Re: Using finance function

Since you code looks straight out of the online help you should have also found the bit with:

FINANCE('XIRR', values, dates, <guess>);

So you place your value and date variables (in corresponding order : value variable1, value variable 2 ..., value variable n, Date variable 1, ...date variable n, and the "guess" variable if any.

 

Note: for use with an existing data set the date variables should be SAS date values.

 

If you want more specific help you will have to post an example data set. If you post it as data step code you will be more likely to get specific results.

 

The generic approac will be:

 

Data want;

   set have; /* this is the name of your existing data set with the value and date variables*/

   r = finace('xirr',v1,v2,d1,d2,0.1);  /*<= replace V with the names of your existing value variable and D with the names of existing date variable*/

run;

Contributor
Posts: 30

Re: Using finance function

I tried, the variables I have are :

ctza_cierre as numeric, 

fecha and maturity as date format (example: 29MAR2016)

 

The code I used was;

 

Data want;
set QUERY_FOR_APPEND_TABLE; 
r = finance('xirr',ctza_cierre,1,fecha,maturity,0.1); 
run;

 

The log error is:

ERROR: The algorithm for finding XIRR failed to converge.
ERROR: The algorithm for finding XIRR failed to converge.
ERROR: The algorithm for finding XIRR failed to converge.
ERROR: The algorithm for finding XIRR failed to converge.
ERROR: The algorithm for finding XIRR failed to converge.

 

Do you know where could be the problem?

 

Thanks and kind regards,

 

Super User
Posts: 10,527

Re: Using finance function

Please provide exact values used.

When I see something like value of 1 for the value you may have provided data that when compared with the other value will not "work" hence the fail to converge.

Or remove the guess completely. If you don't have any idea what the value might be close to and providing a value you may have specified a starting point that can't get to where you need to go.

 

The convergence message basically says: SAS tried varying values for the possible result and could not get a feasible answer after trying a number of different values.

Contributor
Posts: 30

Re: Using finance function

 

 

Thank you very much!!!

 

With your reply I finally get the result. 

 

The problem was with onw of the cashflows. 

 

Thanks and kind regards!!

 

Ask a Question
Discussion stats
  • 4 replies
  • 783 views
  • 0 likes
  • 2 in conversation