Help in calculating 5-year compounded return on monthly data

[danielhu]

Hi, I have a large data of monthly stock price from 1985 Aug-1990 July. And I am required to calculate the 5 year compounded return for each stock. How do i go about it? An example would as below, from table A to B. The tricky thing not all stocks have full monthly data from 1985 Aug to 1990 July and there are blanks data too.

table A

Cusip	Months	price
111111	1985 Aug	2.05
111111	…	…
111111	1990 Jul	5.36
100000	1985 Aug	5.8
100000	…	…
100000	1990 Jul	8.6

table b

Cusip	Months	price	5 years return
111111	1985 Aug	2.05	xxx
111111	…	…
111111	1990 Jul	5.36
100000	1985 Aug	5.8	xxx
100000	…	…
100000	1990 Jul	8.6

[Reeza]

What's your formula?

Have you looked into the Financial formula's?

Can you post a worked example with data and expected output?

[Steelers_In_DC]

If you are subtracting beginning price from end price here you go:

data have;
infile cards dsd;
input Cusip$ Months$ price;
cards;
111111,1985 Aug,2.05
111111,,,
111111,1990 Jul,5.36
100000,1985 Aug,5.8
100000,,
100000,1990 Jul,8.6
;

data prep;
set have;
if not missing(months) then do;
_month = month(input(put(cats('01',scan(months,2,' '),2015),$9.),date9.));
end;
_year = scan(months,1,' ');
run;

proc sort data=prep out=prep2;by cusip _year _month;where not missing(months);

data want;
set prep2;
retain _begin;
by cusip _year _month;
if first.cusip then _begin = price;
if last.cusip then _end = price;
return = _end-_begin;
drop _:;
run;