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,
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.
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.
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
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.
Is that clearer?
Not even in the slightest. Best of luck.
Look at the FINANCE function, which enables you to compute the IRR.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.