A B C D E F G H J K L Stock Returns and Variance Estimating stock returns, variance and standard deviation. Problem 1: You bought 100 shares of Starbucks Corp. (SBUX) 7 years ago (1Aug '95) for $5.00 per share and sold the 100 shares today for $20.31 each. What are your returns? At the same time your sister bought 100 shares of Coca-Cola (KO). How did your returns compare 10 (excluding brokerage fees)? Assume today is July 1, 2002 and Coke paid cumulative dividends of $4.94. 11 12 13 Data: Monthly Closing Prices From March 1995 to July 2002 for Coca-Cola and Starbucks. 14 15 Date KO SBUX 16 1-Jul-02 19.43 20.31 Step 1: Capital Appreciation 17 3-Jun-02 56.00 24.85 18 1-May-02 55.35 24.28 A. Price you received today 19 1-Apr-02 55.30 22.82 Price you paid 20 1-Mar-02 52.07 23.13 Difference 21 1-Feb-02 47.02 23.01 * 100 shares 22 2-Jan-02 43.41 23.77 23 3-Dec-01 46.78 19.05 In 1995 you spent $500 for 100 shares of SBUX. 24 1-Nov-01 46.59 17.72 Today, when you sold them, your capital appreciation 25 1-Oct-01 47.33 17.12 is valued at = => 26 4-Sep-01 46.31 14.94 27 1-Aug-01 48.11 16.87 B At the same time your sister bought 100 shares of 28 2-Jul-01 44.08 18.04 Coca Cola. What was her stock's capital appreciation? 29 1-Jun-01 44.48 23.00 30 1-May-01 46.67 19.52 Price she received today 31 2-Apr-01 45.48 19.35 Price she paid 32 1-Mar-01 44.46 21.22 Difference 33 1-Feb-01 52.02 23.81 . 100 shares 34 2-Jan-01 56.90 24.97 Today, when she sold them, her capital appreciation 35 1-Dec-00 59.78 22.12 is valued at => 36 1-Nov-00 61.44 22.78 37 2-Oct-00 59.06 22.34 38 1-Sep-00 53.93 20.03 39 1-Aug-00 51.33 18.31 Step 2: Dividends Dividends are given. 40 3-Jul-00 19.74 18.75 41 1-Jun-00 56.01 19.09 A Dividends per share of SBUX 42 1-May-00 51.88 17.00 * 100 shares 43 3-Apr-00 45.93 15.12 Div earnings from SBUX 44 1-Mar-00 45.62 22.41 45 1-Feb-00 47.09 17.56 46 3-Jan-00 55.62 16.00 47 1-Dec-99 56.41 12.12 B. Dividends per share of KO 4.94 48 1-Nov-99 65.18 13.28 * 100 shares 49 1-Oct-99 57.00 13.59 Div earnings from KO 50 1-Sep-99 46.61 12.39A B C D E F G H J K L 51 2-Aug-99 57.61 11.44 1-Jul-99 58.34 11.62 Step 3: Total Return Percentage for Holding Period 53 1-Jun-99 59.72 18.78 54 3-May-99 65.82 18.44 A SBUX cost (price X 100) 55 1-Apr-99 65.40 18.47 SBUX capital appreciation 56 1-Mar-99 8.98 14.03 SBUX dividends 57 1-Feb-99 61.22 13.22 SBUX cap. app. + div. 58 4-Jan-99 62.60 13.02 Total return = (CA + D) / cost 59 1-Dec-98 64.22 14.03 60 2-Nov-98 67.15 11.53 61 1-Oct-98 64.62 10.84 B KO cost (price X 100) 62 1-Sep-98 5.12 9.05 KO capital appreciation 63 3-Aug-98 62.14 7.89 KO dividends ($4.94 per share) 64 1-Jul-98 76.81 10.47 KO cap. app. + div. 65 1-Jun-98 81.58 13.36 Total return = (CA + D) / cost 66 1-May-98 74.64 2.00 67 1-Apr-98 72.26 12.03 For the 6 years and 11 months you and your sister held your 68 2-Mar-98 73.75 11.33 stocks, your stock return was and your 69 2-Feb-98 65.22 9.89 sister's was => 70 2-Jan-98 61.54 9.14 71 1-Dec-97 63.38 9.59 72 3-Nov-97 59.40 8.72 73 1-Oct-97 53.70 8.25 Step 4: One Year Total Return Percentage 74 2-Sep-97 57.85 10.45 75 1-Aug-97 54.22 10.25 A SBUX price on Jul 02*100 76 1-Jul-97 65.39 10.23 SBUX price on Jul 01 *100 77 2-Jun-97 64.33 9.73 SBUX dividends 78 1-May-97 64.67 7.88 capital appreciation 79 1-Apr-97 60.07 7.47 SBUX cap. app. + div. 80 3-Mar-97 52.63 7.41 TR = (CA + D) / price Julol 81 3-Feb-97 57.46 8.41 82 2-Jan-97 54.52 8.56 83 2-Dec-96 49.57 7.16 B. KO price on Jul 02*100 84 1-Nov-96 48.16 8.66 KO price on Jul 01 *100 85 1-Oct-96 47.45 8. 12 KO dividends ( 76 per sh) 86 3-Sep-96 47.81 8.25 capital appreciation 87 1-Aug-96 46.87 8.19 KO cap. app. + div. 88 1-Jul-96 43.94 6.50 TR = (CA + D) / price Ju101 89 3-Jun-96 45.93 7.06 90 1-May-96 43.01 6.78 91 1-Apr-96 38.10 6.78 Question 1. Which stock did better over the holding period? 92 1-Mar-96 38.68 5.83 93 1-Feb-96 37.63 4.41 94 2-Jan-96 35.13 4.19 Question 2. Which stock did better over the las 95 1-Dec-95 34.60 5.25 96 1-Nov-95 35.30 5.28 97 2-Oct-95 33.40 4.91 Question 3. Are you surprised? 98 1-Sep-95 32.06 4.73 1- Aug-95 29.75A B C D E F G H J K L 102 103 Problem 2: The following table shows the historical returns for large company stocks from 1980-1999. Let's find the average return and the standard deviation of the large firm returns. 104 105 Data: Yearly Historical Returns for U. S. Large Company Stocks 1980-1999. 106 Large Company 107 Year Stocks 108 1980 32.61% Step 1: Average Return 109 1981 4.97% 110 1982 21.67% Calculate the historical average return for large co. stocks 111 1983 22.57% 112 1984 6.19% Use the Excel function: =average(cell range)/100 113 1985 31.85% We need to divide by 100 because the values are in per cent. 114 1986 18.68% 115 1987 5.22% A. In cell J1 16 enter: = average(C108:C127) 116 1988 16.58% 117 1989 31.75% 118 1990 -3.13% Step 2: Return Distribution 119 1991 30.53% 120 1992 7.62% Calculate the variance and standard deviation of large firm 121 1993 10.07% historical returns. 122 1994 1.27% 123 1995 37.80% Variance is the average of the squared deviations from the mean. 124 1996 22.74% We calculate the deviation of each individual return from the mean 125 1997 33.43% (average), square those numbers, sum the squares, and then divide 126 1998 28.13% by the number of returns minus one. 127 1999 21.03% 128 The standard deviation is the square root of the variance. It is in 129 percentage form and is used to make comparisons. 130 131 Large Co. Average Squared 132 Year Return Return Deviation Deviation 133 980 32.61% 134 1981 4.97% 135 1987 21.67% 136 1983 22.57% Steps for calculating standard deviation: 137 1984 6.19% 138 1985 31.85% Hint: range means a row or column of cells 139 1986 18.68% 140 1987 5.22% A. Enter the 1980-1999 historical average 141 1988 16.58% from J116. Copy from year 1 to 20. 142 10 1989 31.75% 143 11 1990 -3.13% 144 B. Find the yearly deviation by subtracting 1991 30.53% the average return from the Lg Co Ret. 145 13 1992 7.62% Copy from year 1 to 20. 146 14 1993 10.07% 147 15 1994 1.27% C. Find the squared deviation by multiplyingA B C D E F G H K L M 147 15 1994 1.27% C. Find the squared deviation by multiplying 148 16 1995 37.80% the deviation by itself. Copy. 149 17 1996 22.74% 150 18 1997 33.43% D. In cell F153 enter: =sum(range)/(20-1) 151 19 1998 28.13% 152 20 1999 21.03% E. In cell F154 take the square root of F153 153 st dev varianc Enter: = sqrt(F153 154 st dev 155 156 157 F. We can use the built-in formula: in cell C153 enter: =stdev.s(range). C153 should be equal to F154 (13.13%). 158 159 160 Test Your Skills: 161 162 Question 1: A stock had annual returns of 19 percent, -5 percent, 14 percent, 16 percent, 1 1 percent, & 12 percent 163 each for the past six years. What is the average return and standard deviation for this 164 stock? 165 166 Year Return 167 168 169 170 171 172 173 174 Average return 175 Standard deviation 176 177 178 Question 2: A stock had annual returns of -1 percent, -5 percent, 0 percent, 16 percent, 25 percent, and 50 percent 179 each for the past six years. What is the average return and standard deviation for this stock? 180 181 Year Return 182 183 184 185 186 187 188 189 Average return 190 Standard deviation 191 192 193 194 195 96