Proc FCMP - How to approximate the internal rate of return?

[Frieda]

Dear All, 

For a project at my university, I like to calculate the internal rate of return for multiple projects. That is, I have data on the "price" of today P, the future monthly Cash flows (CF) for the next 5 years and then a growth assumption for these Cash flows up to infinity. Now, I like to calculate the internal rate of return which discounts the future cash flows to the price of today. I was told its easiest to use proc fcmp for that, but i am not experienced with that...

I uploaded an image of the formula, I need to solve for r - i have data for the rest of the variables. 

I'd be super thankful if someone could help me, as I am starting to get desperated 😞

(I am a SAS newbe, please excuse my unawareness of basic things).

Thank you! 

[PGStats]

Do not despair! What parameters would you pass to such a function?

[Frieda]

Hi, 

Well, I got the price of today and the future cash flow amounts in US dollars. The r is the value that is searched for in decimals. 

Frieda

[gergely_batho · Accepted solution]

Hi,

First look at Financial functions in SAS Functions and CALL Routines by Category:

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0w6napahk...

Your required function might be there. If not...

Your cash flow is quite special, so maybe you really need to use PROC FCMP to solve for r.

This is how I would do it:

I'm starting from the SOLVE Function code examples here:

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p1xoknqns865t7n1wehj6...

proc fcmp;
      /* define the present value function*/
   function present_value(r, cf1,cf2,cf3,cf4,cf5, cf6_oo);
      array cfs[6] cf1 cf2 cf3 cf4 cf5 cf6_oo;
 p=0;
 do t=1 to 5;
 p=p + cfs[t]/(1+r)**t + cfs[t+1]/(r*(r+1)**t); /*This is your formula*/
 end;
 return (p);
 endsub;
 function get_r(pv, cf1,cf2,cf3,cf4,cf5, cf6_oo);
 r=solve('present_value', {.}, pv, ., cf1,cf2,cf3,cf4,cf5, cf6_oo);
 return(r);
 endsub;

data want;
 set have;
 r=get_r(pv, cf1,cf2,cf3,cf4,cf5, cf6_oo);
run;

Note, you might need to specify a starting value for r in the solve() functoon call. (See: {.})

Sorry, I have not run the code - no syntax check at all.

Hope this helps.

Greg

[Frieda]

Thank you! I will try to apply this, and see how far it takes me towards my desired outcome 🙂

[ballardw]

You may want to investigate the SAS supplied functions IRR and INIRR or the FINANCE function IRR, MIRR, XIRR options before doing a lot of work unless those don't meet your needs.