I have a dataset that includes each project's initial date and initial cost, as well as the date and amount of cash flows:
data have;
input project $1. date_initial :yymmdd10. date_cf :yymmdd10. cf cost_initial 5.;
format date_initial date_cf yymmdd10.;
datalines;
A 2004-08-25 2005-03-23 3 30
A 2004-08-25 2005-06-20 4 30
A 2004-08-25 2005-09-25 6 30
A 2004-08-25 2005-12-23 4 30
A 2004-08-25 2006-04-05 7 30
A 2004-08-25 2006-07-05 8 30
B 2007-06-27 2008-01-22 4 45
B 2007-06-27 2008-04-21 6 45
B 2007-06-27 2008-07-21 7 45
B 2007-06-27 2008-10-20 8 45
B 2007-06-27 2009-01-20 9 45
B 2007-06-27 2009-04-20 10 45
B 2007-06-27 2009-07-20 12 45
B 2007-06-27 2009-11-05 11 45
B 2007-06-27 2010-02-22 9 45
;
run;
I want to compute the internal rate of return (IRR) for each project and each observation. That is, after each cash flow, I want an updated IRR at the cash flow date. The IRR at the date of cash flow n is the solution of
where t is the time between the initial date and the date of a cash flow. For example, the IRR for the 2nd observation should solve
and
t1 = intck('day', '25aug2004'd, '23mar2005'd) / 365
t2 = intck('day', '25aug2004'd, '30jun2005'd) / 365
As the cash flow dates are not strictly quarterly, SAS IRR function (which requires a constant frequency) does not seem to work in this case.
>I want the output to be similar to dataset "have", but includes an additional variable as the IRR computed for each observation, >using all cash flows to the project_id up to the date_cf
Based on your query, below might be what you are expecting but I can't tell since there is no firm description of how you want your output to be like.
I hope you clarify it first.
data want;
infile datalines dlm="09"x;
input project $1. date_initial :yymmdd10. date_cf :yymmdd10. cf cost_initial r;
datalines;
1 A 2004-08-25 2005-03-23 3 30 -0.981723006
2 A 2004-08-25 2005-06-20 4 30 -0.864252017
3 A 2004-08-25 2005-09-25 6 30 -0.603330209
4 A 2004-08-25 2005-12-23 4 30 -0.430594664
5 A 2004-08-25 2006-04-05 7 30 -0.171907236
6 A 2004-08-25 2006-07-05 8 30 0.0493847285
7 B 2007-06-27 2008-01-22 4 45 -0.985403284
8 B 2007-06-27 2008-04-21 6 45 -0.870849089
9 B 2007-06-27 2008-07-21 7 45 -0.667472842
10 B 2007-06-27 2008-10-20 8 45 -0.435187718
11 B 2007-06-27 2009-01-20 9 45 -0.212882285
12 B 2007-06-27 2009-04-20 10 45 -0.017025432
13 B 2007-06-27 2009-07-20 12 45 0.1624599799
14 B 2007-06-27 2009-11-05 11 45 0.2868471891
15 B 2007-06-27 2010-02-22 9 45 0.3659270159
;
run;
>To use the finance function, it seems that I need to create many variables that contain the value of previous cash flows, so that >I can call the function for every observation. One concern is that each observation corresponds to different number of cash >flows. Is there any good way to achieve this?
One idea would be to use array statement to generate multiple variables and do running irr calculation for every observation in a do loop along with _N_ automatic variable.
Again, this is a sample code based on my understanding stated above.
data have;
input project $1. date_initial :yymmdd10. date_cf :yymmdd10. cf cost_initial 5.;
format date_initial date_cf yymmdd10.;
datalines;
A 2004-08-25 2005-03-23 3 30
A 2004-08-25 2005-06-20 4 30
A 2004-08-25 2005-09-25 6 30
A 2004-08-25 2005-12-23 4 30
A 2004-08-25 2006-04-05 7 30
A 2004-08-25 2006-07-05 8 30
B 2007-06-27 2008-01-22 4 45
B 2007-06-27 2008-04-21 6 45
B 2007-06-27 2008-07-21 7 45
B 2007-06-27 2008-10-20 8 45
B 2007-06-27 2009-01-20 9 45
B 2007-06-27 2009-04-20 10 45
B 2007-06-27 2009-07-20 12 45
B 2007-06-27 2009-11-05 11 45
B 2007-06-27 2010-02-22 9 45
;
run;
data want;
do _N_=1 by 1 until(last.project);
set have;
by project;
*generate date_cf by observation;
array _date_cf[365] ;
*generate _cf by observation;
array _cf[365];
*assign date_cf variable value one by one by observation;
_date_cf[_N_]=date_cf;
*assign _cf variable value one by one by observation;
_cf[_N_]=cf;
*assign 0 if variables have missing values;
do i=1 to dim(_date_cf);
if missing(_date_cf[i]) then _date_cf[i]=0;
if missing(_cf[i]) then _cf[i]=0;
end;
*calculate irr;
r_byproject=finance('xirr', -cost_initial, of _cf[*], date_initial, of _date_cf[*]);
output;
end;
drop i _:;
run;
There is a finance function that matches your need.
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarg...
However, I'm more curious as to how you want your output to be like.
Is it detailed in the query?
I want the output to be similar to dataset "have", but includes an additional variable as the IRR computed for each observation, using all cash flows to the project_id up to the date_cf.
To use the finance function, it seems that I need to create many variables that contain the value of previous cash flows, so that I can call the function for every observation. One concern is that each observation corresponds to different number of cash flows. Is there any good way to achieve this?
>I want the output to be similar to dataset "have", but includes an additional variable as the IRR computed for each observation, >using all cash flows to the project_id up to the date_cf
Based on your query, below might be what you are expecting but I can't tell since there is no firm description of how you want your output to be like.
I hope you clarify it first.
data want;
infile datalines dlm="09"x;
input project $1. date_initial :yymmdd10. date_cf :yymmdd10. cf cost_initial r;
datalines;
1 A 2004-08-25 2005-03-23 3 30 -0.981723006
2 A 2004-08-25 2005-06-20 4 30 -0.864252017
3 A 2004-08-25 2005-09-25 6 30 -0.603330209
4 A 2004-08-25 2005-12-23 4 30 -0.430594664
5 A 2004-08-25 2006-04-05 7 30 -0.171907236
6 A 2004-08-25 2006-07-05 8 30 0.0493847285
7 B 2007-06-27 2008-01-22 4 45 -0.985403284
8 B 2007-06-27 2008-04-21 6 45 -0.870849089
9 B 2007-06-27 2008-07-21 7 45 -0.667472842
10 B 2007-06-27 2008-10-20 8 45 -0.435187718
11 B 2007-06-27 2009-01-20 9 45 -0.212882285
12 B 2007-06-27 2009-04-20 10 45 -0.017025432
13 B 2007-06-27 2009-07-20 12 45 0.1624599799
14 B 2007-06-27 2009-11-05 11 45 0.2868471891
15 B 2007-06-27 2010-02-22 9 45 0.3659270159
;
run;
>To use the finance function, it seems that I need to create many variables that contain the value of previous cash flows, so that >I can call the function for every observation. One concern is that each observation corresponds to different number of cash >flows. Is there any good way to achieve this?
One idea would be to use array statement to generate multiple variables and do running irr calculation for every observation in a do loop along with _N_ automatic variable.
Again, this is a sample code based on my understanding stated above.
data have;
input project $1. date_initial :yymmdd10. date_cf :yymmdd10. cf cost_initial 5.;
format date_initial date_cf yymmdd10.;
datalines;
A 2004-08-25 2005-03-23 3 30
A 2004-08-25 2005-06-20 4 30
A 2004-08-25 2005-09-25 6 30
A 2004-08-25 2005-12-23 4 30
A 2004-08-25 2006-04-05 7 30
A 2004-08-25 2006-07-05 8 30
B 2007-06-27 2008-01-22 4 45
B 2007-06-27 2008-04-21 6 45
B 2007-06-27 2008-07-21 7 45
B 2007-06-27 2008-10-20 8 45
B 2007-06-27 2009-01-20 9 45
B 2007-06-27 2009-04-20 10 45
B 2007-06-27 2009-07-20 12 45
B 2007-06-27 2009-11-05 11 45
B 2007-06-27 2010-02-22 9 45
;
run;
data want;
do _N_=1 by 1 until(last.project);
set have;
by project;
*generate date_cf by observation;
array _date_cf[365] ;
*generate _cf by observation;
array _cf[365];
*assign date_cf variable value one by one by observation;
_date_cf[_N_]=date_cf;
*assign _cf variable value one by one by observation;
_cf[_N_]=cf;
*assign 0 if variables have missing values;
do i=1 to dim(_date_cf);
if missing(_date_cf[i]) then _date_cf[i]=0;
if missing(_cf[i]) then _cf[i]=0;
end;
*calculate irr;
r_byproject=finance('xirr', -cost_initial, of _cf[*], date_initial, of _date_cf[*]);
output;
end;
drop i _:;
run;
This works!
Is the number 365 arbitrarily chosen, just to be large enough?
>Is the number 365 arbitrarily chosen, just to be large enough?
It is chosen.I thought there might be a situation to generate variables for a whole year.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.