BookmarkSubscribeRSS Feed
MikeFranz
Quartz | Level 8

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,

 

 

 

 

 

 

 

 

8 REPLIES 8
ballardw
Super User

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.

MikeFranz
Quartz | Level 8

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.

HB
Barite | Level 11 HB
Barite | Level 11

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


 

MikeFranz
Quartz | Level 8
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!
HB
Barite | Level 11 HB
Barite | Level 11

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.

MikeFranz
Quartz | Level 8
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
HB
Barite | Level 11 HB
Barite | Level 11

Is that clearer?

 

Not even in the slightest. Best of luck.

Rick_SAS
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2267 views
  • 1 like
  • 4 in conversation