topic Re: Calculating IRR / Setting up dataset for IRR calc in SAS Programming

Calculating IRR / Setting up dataset for IRR calc

MikeFranz — Wed, 02 Aug 2017 16:03:34 GMT

Hi,

I have a dataset in the following format:

Property   Draw_Date_1 Draw_Amount1 ... Draw_DateX Draw_AmountX final_Value_Date final_Value
Property1  01/01/2015  -10,000      ... 16/07/2015 -40,000      31/12/2015       150,000
Property2  03/04/2015  -20,000      ... 18/07/2016 .            31/12/2016       140,000

As you can see in the above, each row represents one property, with the payment dates and amounts as the variables. Not each property has a drawdown for every drawdown date, in which case the corresponding amount and dates are set to missing.

I would like, for each property, to calculate the IRR, using each drawdown amount/date as an outflow, and the final value/date as the eventual inflow.

Any ideas on how to do this would be much appreciated.

Many thanks,

Re: Calculating IRR / Setting up dataset for IRR calc

ballardw — Wed, 02 Aug 2017 15:41:00 GMT

You might define what IRR stands for, and possibly point to a reference, for those of us that don't work in your realestate (?) field.

Note that SAS has an IRR function but the values you post do not seem to correspond to what is used by that function.

Re: Calculating IRR / Setting up dataset for IRR calc

MikeFranz — Wed, 02 Aug 2017 16:02:59 GMT

Hi, good point.

IRR is the internal rate of return. Is it the interest rate which makes the present value of a series of cashflows equal to zero.

So, for instance, if we have monthly ouflows (payments) into a trading account for 12 months of $100, and the final value of the investment after 12 months $1,300, then the IRR is the interest rate at which one would discount all these cashflows to result in a zero present value.

Here is a link to an article that explains it more clearly than I have: http://www.investopedia.com/terms/i/irr.asp

I will add a "finance" tag to the question, thanks for the advice.

Re: Calculating IRR / Setting up dataset for IRR calc

HB — Wed, 02 Aug 2017 16:06:29 GMT

Any ideas on how to do this

The first idea might be a better (normalized) data structure.

Given data in the format:

data property_data;
   input property eventdate MMDDYY10. eventamount eventtype $1.;
datalines;
1 01/01/2015 -10000 d
1 07/16/2015 -40000 d
1 12/31/2015 150000 f
2 03/15/2016 -10000 d
2 03/16/2016 -10000 d
2 03/17/2016 -10000 d
2 03/18/2016 50000 f
3 12/01/2017 -50000 d
3 12/02/2017 -50000 d
3 12/03/2017 -50000 d
3 12/04/2017 -50000 d
3 12/31/2017 250000 f
;
run;

where an event type of d is a draw and event type of f is a final valuation. You can run proc sql

proc sql;
create table internal_return_rate as
select property, 
sum(case when eventtype = 'd' then eventamount else 0 end) as total_draw, 
sum(case when eventtype = 'f' then eventamount else 0 end) as final_amount 
from property_data
group by property
order by property;
quit;

and come up with

                                                  total_    final_
                              Obs    property      draw     amount

                               1         1        -50000    150000
                               2         2        -30000     50000
                               3         3       -200000    250000

Re: Calculating IRR / Setting up dataset for IRR calc

MikeFranz — Wed, 02 Aug 2017 16:10:10 GMT

Hi! Thanks for the reply. I understand what you're doing, but perhaps the issue comes back (as mentioned by previous contributor) to me not explaining what IRR is. I've included a separate link in the above to explain the function. Thanks again!

Re: Calculating IRR / Setting up dataset for IRR calc

HB — Wed, 02 Aug 2017 16:16:09 GMT

I understand IRR. You use it to take into account the time value of money for payments in the future. Your data is payments already made. Perhaps you should provide more detail on exactly what you want to do.

Re: Calculating IRR / Setting up dataset for IRR calc

MikeFranz — Wed, 02 Aug 2017 16:37:55 GMT

Yes, sure. Each row can be considered a separate "project". So, I have a cash outflow on each Draw_Date, of amount Draw_Amount for each project. I then have a terminal cash inflow on final_Value_Date of amount final_Value_Amount. I would like to calculate the IRR of these cashflows for each project. Is that clearer? Thanks

Re: Calculating IRR / Setting up dataset for IRR calc

HB — Wed, 02 Aug 2017 16:47:40 GMT

Is that clearer?

Not even in the slightest. Best of luck.

Re: Calculating IRR / Setting up dataset for IRR calc

Rick_SAS — Wed, 02 Aug 2017 18:23:53 GMT

Look at the FINANCE function, which enables you to compute the IRR.