turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Studio
- /
- Need new variable for annualized returns

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-05-2018 06:36 PM

it coincides with data from 12/31 it seems:

row | date | ticker ret annual_return |

102 | 20121231 | 40076410 | . | . |

103 | 20121231 | 25374820 | . | . |

104 | 20121231 | 44845110 | . | . |

105 | 20121231 | 68402320 | . | . |

106 | 20121231 | 9.8386E34 | . | . |

107 | 20121231 | 37897340 | . | . |

108 | 20131231 | 85812220 | . | . |

109 | 20131231 | 63637520 | . | . |

110 | 20131231 | 42774610 | . | . |

111 | 20131231 | 14163310 | . | . |

112 | 20131231 | 23275110 | . | . |

113 | 20131231 | 23305179 | . | . |

114 | 20131231 | 23305181 | . | . |

115 | 20131231 | 23305182 | . | . |

116 | 20131231 | 26846137 | . | . |

117 | 20131231 | 26846138 | . | . |

118 | 20131231 | 26846139 | . | . |

119 | 20131231 | 26878510 | . | . |

120 | 20131231 | 76276010 | . | . |

121 | 20131231 | 2.504E13 | . | . |

122 | 20131231 | 9.73E12 | . | . |

123 | 20131231 | 1962130 | . | . |

124 | 20131231 | 19063210 | . | . |

125 | 20131231 | 25303110 | . | . |

126 | 20131231 | 24549610 | . | . |

127 | 20131231 | 76129910 | . | . |

128 | 20131231 | 8.9656E14 | . | . |

129 | 20131231 | 92209930 | . | . |

130 | 20131231 | 65651220 | . | . |

131 | 20131231 | 55347740 | . | . |

132 | 20131231 | 45875130 | . | . |

133 | 20131231 | 50345960 | . | . |

134 | 20131231 | 92870310 | . | . |

135 | 20131231 | 8.34E24 | . | . |

136 | 20131231 | 88831430 | . | . |

137 | 20131231 | 3850540 | . | . |

138 | 20131231 | 96823520 | . | . |

139 | 20131231 | 5.5315E14 | . | . |

140 | 20131231 | 40076410 | . | . |

141 | 20131231 | 25374820 | . | . |

142 | 20131231 | 37897340 | . | . |

143 | 20141231 | 85812220 | . | . |

144 | 20141231 | 63637520 | . | . |

145 | 20141231 | 35168080 | . | . |

146 | 20141231 | 22190710 | . | . |

147 | 20141231 | 8.9656E14 | . | . |

148 | 20141231 | 65651220 | . | . |

149 | 20141231 | 45875130 | . | . |

150 | 20141231 | 8.34E24 | . | . |

151 | 20141231 | 88831430 | . | . |

152 | 20141231 | 40076410 | . | . |

153 | 20141231 | 25374820 | . | . |

154 | 20141231 | 74939710 | . | . |

155 | 20151231 | 67068710 | . | . |

156 | 20151231 | 45875130 | . | . |

157 | 20151231 | 8.34E24 | . |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-05-2018 06:42 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-05-2018 06:43 PM - edited 03-05-2018 06:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-05-2018 07:38 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-05-2018 07:40 PM

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)?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-05-2018 08:19 PM - edited 03-05-2018 08:21 PM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-06-2018 11:14 AM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-06-2018 11:23 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-06-2018 02:54 PM

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

row date ticker cusip ret year annual_return

2 | 20171229 | AA | 1387210 | 0.29776 | 2017 | 11819.254136 |

3 | 20171229 | AABA | 2134610 | -0.002997 | 2017 | 2712.7647923 |

4 | 20141231 | AAC | 30710 | 0.071379 | 2014 | 12518.673092 |

5 | 20151231 | AAC | 30710 | -0.223309 | 2015 | -3079.163211 |

6 | 20161230 | AAC | 30710 | -0.137068 | 2016 | -5451.445011 |

7 | 20171229 | AAC | 30710 | -0.014239 | 2017 | 2296.3094805 |

8 | 20101231 | AAME | 4820910 | 0.068421 | 2010 | 6160.2636444 |

9 | 20111230 | AAME | 4820910 | -0.01005 | 2011 | -304.0471414 |

10 | 20121231 | AAME | 4820910 | 0.064407 | 2012 | 6339.4558727 |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-06-2018 02:59 PM

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%

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-06-2018 03:59 PM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-06-2018 04:24 PM - edited 03-06-2018 04:28 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-06-2018 04:34 PM

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%?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sastuck

03-06-2018 04:41 PM - edited 03-06-2018 04:43 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-06-2018 04:47 PM

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 |

2 | 20170131 | AA | 1387210 | 0.298077 | 2017 |

3 | 20170228 | AA | 1387210 | -0.051029 | 2017 |

4 | 20170331 | AA | 1387210 | -0.005493 | 2017 |

5 | 20170428 | AA | 1387210 | -0.019477 | 2017 |

6 | 20170531 | AA | 1387210 | -0.023421 | 2017 |

7 | 20170630 | AA | 1387210 | -0.008804 | 2017 |

8 | 20170731 | AA | 1387210 | 0.114855 | 2017 |

9 | 20170831 | AA | 1387210 | 0.205494 | 2017 |

10 | 20170929 | AA | 1387210 | 0.062443 | 2017 |

11 | 20171031 | AA | 1387210 | 0.024882 | 2017 |

12 | 20171130 | AA | 1387210 | -0.131226 | 2017 |

13 | 20171229 | AA | 1387210 | 0.29776 | 2017 |