Daily IRR

[HGimenez]

Hi!

I want to calculate a daily IRR. In theory I could use FINANCE XIRR to calculate the annual IRR and then get the daily IRR, but when the IRR is too high or too close to -100% it fails to converge, even if I set a guess value close to the solution

What is the best option for this? PROC NLIN, PROC MODEL or PROC IML?

If you're not familiar with IRR, I have a set of dates and cashflows. I need to find an interest rate that makes the sum of the present values equal to zero. In other words, I need to find a value for daily_irr that satisfies the following equation

SUM ( cashflow[i] / ((1 + daily_irr) ** ndays[i])   ) = 0

Where ** is the power operator and ndays[i] is the amount of days elapsed between the first cashflow and cashflow[i]

There is a sample dataset below. The daily_irr for that dataset would be 2.11%

DATA irr;
input day :ddmmyy8. cashflow;
format day date9.;
fomat cashflow dollarx12.2;
datalines;
	31-03-19 30
	09-04-19 10
	08-05-19 10
	13-06-19 10
	30-06-19 -300
;
run;

DATA irr;
	SET irr;
	RETAIN first_day;
	FORMAT first_day date9.;
	if _N_ = 1 THEN first_day = day;
	ndays = day - first_day;
	keep ndays cashflow;
	RUN;

proc print data=irr; run;

Thanks a lot!!

[ballardw]

Not that I understand the this financial function, or most of them, but it might be a good idea to show how you attempted to use the Finance IRR function. Especially one of the examples that fails to converge. Someone with more expertise might have an 'Aha!' with an example.

[mkeintz · Accepted solution]

Can you give us some data that fails to converge using the finance('XIRR',...) function?  Using that function on the data you provide generates results as you suggest.  I get

       annual_rate=2024.7843291

and

       daily_rate=0.0210785653   (using 365 days compounding per year).

data tmp;
   v1=30; d1=mdy(3, 31, 2019);
   v2=10; d2=mdy(4, 9, 2019);  
   v3=10; d3=mdy(5, 8, 2019);
   v4=10; d4=mdy(6, 13, 2019);
   v5=-300; d5=mdy(6, 30, 2019);
   format d: date9. ;
   ann_r=finance('xirr', v1, v2, v3, v4, v5, d1, d2, d3, d4, d5, 0.9);
   put ann_r=;
        
   presval1=exp(log(v1)+ log(1+ann_r)*((d5-d1)/365));
   presval2=exp(log(v2)+ log(1+ann_r)*((d5-d2)/365));
   presval3=exp(log(v3)+ log(1+ann_r)*((d5-d3)/365));
   presval4=exp(log(v4)+ log(1+ann_r)*((d5-d4)/365));
   presval5=v5;
   array _v {5} v1-v5; array _d {5} d1-d5; array _p {5} presval1-presval5;
   do i=1 to 5;
     put i= _v{i}= _d{i}=  _p{i}=;
   end;
   presval_sum=sum(of presval:);
   put presval_sum=;
run ;

Edited addition:  I suspect (but I haven't done a mathematical proof so I don't know it is absolutely true) that you could discount all your values by a compounded daily rate of, say 1%  (i.e. NewV = V * .99**n) where n is the number of elapsed days.  Then perhaps XIRR convergence for the NewV series could be more computationally stable.  And I think you could then take that IRR and de-adjust it to get the corresponding IRR for the original values.  Of course all this would be needed only if your real problem is in unstable numeric convergence.

[HGimenez]

I finally managed to solve this using the finance function

The problem was that I had some cashflows missing. I usually completed missing values with an amount=0 and date=0. Date=0 is 01JAN1960. This works in most of the cases, but when the IRR is too high or too close to -100%, it fails to converge. 

Workaround: complete missing values with a date closer to other cashflows.

Below is a simplified version of my code. The two calls to finance() only differ in the date used.

data irr;
	format d1-d3 d4_v1 d4_v2 DATE9.;
	v1 = 44.77;
	v2 = -1080.59;
	v3 = -151.61;
	v4 = 0;
	d1 = '31Mar2019'd;
	d2 = '12Jun2019'd;
	d3 = '30Jun2019'd;
	d4_v1 = 0;
	d4_v2 = '31Mar2019'd;
	irr_doesnt_converge = finance('xirr', v1, v2, v3, v4, d1, d2, d3, d4_v1);
	irr_converges = finance('xirr', v1, v2, v3, v4, d1, d2, d3, d4_v2);
	run;

proc print data=irr; run; 

Note that if v1 changes to, for instance, 1444.7, in both cases the finance() call converges:

data irr;
	format d1-d3 d4_v1 d4_v2 DATE9.;
	v1 = 1444.77;
	v2 = -1080.59;
	v3 = -151.61;
	v4 = 0;
	d1 = '31Mar2019'd;
	d2 = '12Jun2019'd;
	d3 = '30Jun2019'd;
	d4_v1 = 0;
	d4_v2 = '31Mar2019'd;
	irr_converges_v1 = finance('xirr', v1, v2, v3, v4, d1, d2, d3, d4_v1);
	irr_converges_v2 = finance('xirr', v1, v2, v3, v4, d1, d2, d3, d4_v2);
	run;

proc print data=irr; run; 

Thanks everybody for your comments!!

[Ksharp]

That is called a function root problem. 

@Rick_SAS  wrote many blog about it by IML .

Also you could try Generic Algorithm :

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3099-2019.pdf

And the best choice is using FINANCE() or IRR() function .