08-02-2017 11:26 AM - edited 08-02-2017 12:03 PM
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.
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.
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.
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
08-02-2017 12:10 PM
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.
08-02-2017 12:37 PM