Write and run SAS programs in your web browser

Accepted Solution

Need new variable for annualized returns

Reply
Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

it coincides with data from 12/31 it seems: 

 

row date  ticker     ret         annual_return
10220121231 40076410..
10320121231 25374820..
10420121231 44845110..
10520121231 68402320..
10620121231 9.8386E34..
10720121231 37897340..
10820131231 85812220..
10920131231 63637520..
11020131231 42774610..
11120131231 14163310..
11220131231 23275110..
11320131231 23305179..
11420131231 23305181..
11520131231 23305182..
11620131231 26846137..
11720131231 26846138..
11820131231 26846139..
11920131231 26878510..
12020131231 76276010..
12120131231 2.504E13..
12220131231 9.73E12..
12320131231 1962130..
12420131231 19063210..
12520131231 25303110..
12620131231 24549610..
12720131231 76129910..
12820131231 8.9656E14..
12920131231 92209930..
13020131231 65651220..
13120131231 55347740..
13220131231 45875130..
13320131231 50345960..
13420131231 92870310..
13520131231 8.34E24..
13620131231 88831430..
13720131231 3850540..
13820131231 96823520..
13920131231 5.5315E14..
14020131231 40076410..
14120131231 25374820..
14220131231 37897340..
14320141231 85812220..
14420141231 63637520..
14520141231 35168080..
14620141231 22190710..
14720141231 8.9656E14..
14820141231 65651220..
14920141231 45875130..
15020141231 8.34E24..
15120141231 88831430..
15220141231 40076410..
15320141231 25374820..
15420141231 74939710..
15520151231 67068710..
15620151231 45875130..
15720151231 8.34E24.
Super User
Posts: 6,762

Re: Need new variable for annualized returns

You might have to fix the data first then.  If RET is missing, all the calculations based on RET will also be missing.

 

If that's what needs to be fixed, however, it's easy for SAS to remove the observation with a missing RET and base the calculations on the remaining data.

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

[ Edited ]
Posted in reply to Astounding

okay, I will do a datstep to drop observations with missing RET then. Also, I will do some research to see if these annual_return figures are accurate. 

Super User
Posts: 6,762

Re: Need new variable for annualized returns

The DATA step can be "fixed" if it has to deal with years that don't begin in January or that don't end in December.  But it does take a decision as to whether the first year must end in December, and the last year must begin in January and use as many months as appear in the data.

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding
so if it is fixed, then the data set will lose everything that doesn’t fit that parameter (starting with jan and ending with dec)?
Super User
Posts: 6,762

Re: Need new variable for annualized returns

[ Edited ]

It will lose the years that don't end with December.  It's not hard to fix the program (or else you could just add the missing December with RET=0).  The other question though is what happens if the first year begins in, say, November.  Should the first year then be 2 months long?

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

Hey @Astounding, thanks for helping me create the variable annual_return! Could I ask what is the source of the equation you used/what it would look like in mathematical form? It's sort of hard to see in the code. Thanks!

Super User
Posts: 6,762

Re: Need new variable for annualized returns

I didn't use a formula, just logic.  Here's the idea.  If you start with $x, and your return is Y% (Y as a decimal fraction), after one time period the value is:

 

$X * (1 + y)

 

That compounds monthly.  At the end of all the time periods, to compute the total return, you have:

 

(Final $X - Original $X) / (Original $X)

 

The math gets simplified when you start with Original $X of 1 each time.

 

You also wanted the percents on a scale of 0 to 100, instead of 0 to 1.  So multiply by 100.

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

Does an annual return of this magnitude make sense? Like an annual return of 11,819%?

 

row            date           ticker       cusip       ret          year          annual_return

220171229AA13872100.29776201711819.254136 
320171229AABA2134610-0.00299720172712.7647923 
420141231AAC307100.071379201412518.673092 
520151231AAC30710-0.2233092015-3079.163211 
620161230AAC30710-0.1370682016-5451.445011 
720171229AAC30710-0.01423920172296.3094805 
820101231AAME48209100.06842120106160.2636444 
920111230AAME4820910-0.010052011-304.0471414 
1020121231AAME48209100.06440720126339.4558727
Super User
Posts: 6,762

Re: Need new variable for annualized returns

Not at all.  What were the monthly values that generated this annual return?

 

Also remember, these values get multiplied by 100 at the end.  So make sure that the result is what you are posting, and not 118.19%

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

also, I don't recall there being a variable in the dataset for dollars ($) earned over the month. We are only dealing with a rate of return (%) as seen in the ret column. Is this correct? 

Super User
Posts: 6,762

Re: Need new variable for annualized returns

[ Edited ]

Dollars earned over the month would be starting value * RET.  But since you're not interested in dollars earned, only in the rate of return, the starting value gets arbitrarily set to $1 at the beginning of each year.  The final rate of return looks at the final value compared to the starting value.  It computes the difference which (based on a starting value of $1) is the rate of return for the year.

 

Take a simple example:  just 2 months in the year, each with a return of 10%.  The calculations ...

 

At the beginning, value is $1.

 

After 1 month, value is $1 * (1 + 10%) = $1.1

 

After 2 months, value is $1.1 * (1 + 10%) = $1.21

 

The combined rate of return compares $1.21 to the original value of $1:

 

($1.21 - $1) / $1 = 21%

 

Being interested in the rate of return only, the original starting value does not really matter.  Say it was $50 at the beginning.

 

After 1 month, value is $50 * (1 + 10%) = $55

 

After 2 months, value is $55 * (1 + 10%) = $60.50

 

The combined rate of return compares $60.50 to the original value of $50:

 

($60.50 - $50) / $50 = 21%

 

Now that I've spelled out these examples, I'll go back and verify that this is the way I applied the formulas.

 

*************** EDITED:

 

Yes, the formulas match, with one exception.  The calculations in this example yield 0.21 as the annualized rate of return.  The problem description looked like you were asking for an answer of 21 instead of 0.21.  So the program multiplies by 100 at the end.  Multiplying by 100 can easily be removed if  you would prefer.

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

Wow, thank you so much for the help and examples. Very helpful. So if this annual_return variable is accurate, which I believe you when you say it is, then say (for example) firm AA had a good year? An annual return of 11819.25%? 

Super User
Posts: 6,762

Re: Need new variable for annualized returns

[ Edited ]

Again, show the monthly returns that appear in the data for AA, for that year.  It should be an easily calculation to verify once we're looking at the monthly returns.

 

One thing to verify, while you are examining the data.  Did the prior year for AA have a December date included in the data?

 

And remember, anything is possible.  Some CEOs decide to take a $1 salary for a while, and later go back to a different system.

Regular Contributor
Posts: 184

Re: Need new variable for annualized returns

Posted in reply to Astounding

I don't have time to try the calculation at the moment, but here is the AA data (there appears to only be one year for it):

 

row date      ticker cusip      ret             year
220170131AA13872100.2980772017
320170228AA1387210-0.0510292017
420170331AA1387210-0.0054932017
520170428AA1387210-0.0194772017
620170531AA1387210-0.0234212017
720170630AA1387210-0.0088042017
820170731AA13872100.1148552017
920170831AA13872100.2054942017
1020170929AA13872100.0624432017
1120171031AA13872100.0248822017
1220171130AA1387210-0.1312262017
1320171229AA13872100.297762017
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 32 replies
  • 336 views
  • 5 likes
  • 4 in conversation