## Help in calculating 5-year compounded return on monthly data

Occasional Contributor
Posts: 17

# Help in calculating 5-year compounded return on monthly data

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
Super User
Posts: 23,724

## Re: Help in calculating 5-year compounded return on monthly data

Have you looked into the Financial formula's?

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

Valued Guide
Posts: 863

## Re: Help in calculating 5-year compounded return on monthly data

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;

Discussion stats
• 2 replies
• 169 views
• 0 likes
• 3 in conversation