topic Re: Proc FCMP - How to approximate the internal rate of return? in SAS Procedures

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

Frieda — Sun, 03 Jul 2016 16:46:17 GMT

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!

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

PGStats — Sun, 03 Jul 2016 17:28:23 GMT

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

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

Frieda — Mon, 04 Jul 2016 06:06:24 GMT

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

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

gergely_batho — Mon, 04 Jul 2016 16:39:48 GMT

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#p0w6napahk6x0an0z2dzozh2ouzm.htm

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#p1xoknqns865t7n1wehj6xarwhdb.htm

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

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

ballardw — Tue, 05 Jul 2016 15:25:29 GMT

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.

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

Frieda — Tue, 05 Jul 2016 15:55:58 GMT

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