topic Re: Solve equations based on values across observations: compute IRR after each cash flow in SAS Programming

Solve equations based on values across observations: compute IRR after each cash flow

xyxu — Thu, 12 Nov 2020 21:13:55 GMT

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.

Re: Solve equations based on values across observations: compute IRR after each cash flow

xyxu — Thu, 12 Nov 2020 21:14:43 GMT

Added more details for the problem. Any suggestions?

Re: Solve equations based on values across observations: compute IRR after each cash flow

hhinohar — Fri, 13 Nov 2020 10:26:25 GMT

There is a finance function that matches your need.
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=p0xrqvy0hix04on1ol882q71xl0e.htm&locale=en

However, I'm more curious as to how you want your output to be like.
Is it detailed in the query?

Re: Solve equations based on values across observations: compute IRR after each cash flow

xyxu — Fri, 13 Nov 2020 14:58:55 GMT

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?

Re: Solve equations based on values across observations: compute IRR after each cash flow

hhinohar — Fri, 13 Nov 2020 15:46:40 GMT

>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;

Re: Solve equations based on values across observations: compute IRR after each cash flow

xyxu — Fri, 13 Nov 2020 18:11:42 GMT

This works!

Is the number 365 arbitrarily chosen, just to be large enough?

Re: Solve equations based on values across observations: compute IRR after each cash flow

hhinohar — Fri, 13 Nov 2020 21:41:18 GMT

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

Re: Solve equations based on values across observations: compute IRR after each cash flow

xyxu — Fri, 13 Nov 2020 21:49:43 GMT

I see. Since we have a date for each cash flow, the IRR function should take care of timing issues and give us an annulized rate, regardless of how many years are involved.