turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Using finance function

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-05-2016 04:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-05-2016 04:47 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-05-2016 04:56 PM

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,

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-05-2016 05:19 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-05-2016 05:26 PM

Thank you very much!!!

With your reply I finally get the result.

The problem was with onw of the cashflows.

Thanks and kind regards!!