turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculating IRR / Setting up dataset for IRR calc

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-02-2017 11:26 AM - edited 08-02-2017 12:03 PM

Hi,

I have a dataset in the following format:

Property Draw_Date_1 Draw_Amount1 ... Draw_DateX Draw_AmountX final_Value_Date final_ValueProperty1 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,

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

08-02-2017 11:41 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

08-02-2017 12:02 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

08-02-2017 12:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-02-2017 12:10 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

08-02-2017 12:16 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-02-2017 12:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

08-02-2017 12:47 PM

Is that clearer?

Not even in the slightest. Best of luck.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MikeFranz

08-02-2017 02:23 PM

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