Stock Returns and Variance Estimating stock retums, variance and standard deviation Problem 1: You bought 100 shares of Starbucks Corp. (SBUX) 7 years ago (1 Aug '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 (excluding brokerage zes)? Assume today is July 1, 2002 and Coke paid cumulative dividends of $4.94 Data: Monthly Closing Prices From March 1995 to July 2002 for Coca-Cola and Starbucks. A Step 1: Capital Appreciation A Price you received today Price you paid Diference 100 shares In 1995 you spent $500 for 100 shares of SBUX Today. When you sold them your capital appreciation is valued at > At the same time your sister bought 100 shares of Coca Cola What was her stock's capital appreciation? Price she received today Price she paid Difference - 100 shares Today, when she sold them her capital appreciation is valued at => B. Date 1-Jul-02 3-Jun-02 1-May-02 1-Apr-02 1-Mar-02 1-Feb-02 2-Jan-02 3-Dec-01 1-Nov-01 1-Oct-01 4-Sep-01 1-Aug-01 2-Jul-01 1-Jun-01 1-May-01 2-Apr-01 1-Mar-01 1-Feb-01 2-Jan-01 1-Dec-00 1-Nov-00 2-Oct-00 1-Sep-00 1-Aug-00 3-Jul-00 1-Jun-00 1-May-00 3-Apr-00 1-Mar-00 1-Feb-00 3-Jan-00 1-Dec-99 1-Nov-99 1-Oct-99 1-Sep-99 2-Aug-99 1-Jul-99 1-Jun-99 3-May-99 1-Apr-99 1-Mar-99 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 49.43 56.00 55.33 55.30 52.07 47.02 43.41 46.78 46.59 47.33 46.31 48.11 44.08 44.48 46.67 45.48 44.46 52.02 56.90 59.78 61.44 59.06 53.93 5133 59.79 56.01 51.88 45.93 45.62 47.09 55.62 56.41 65.18 57.00 46.61 57.61 58.34 59.72 65.82 65.40 58.98 61.22 62.60 64.22 67.15 SBUX 20.31 24.85 24.28 22.82 23.13 23.01 23.77 19.05 17.72 17.12 14.94 16.87 18.04 23.00 19.52 19.35 21.22 23.81 24.97 22.12 22.78 22:34 20.03 18.31 18.75 19.09 17.00 15.12 22.41 1756 Step 2: Dividends Dividends are given 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX 16.00 B. 4.94 Dividends per share of KO * 100 shares Div ezmings from KO Step 3: Total Return Percentage for Holding Period 12.12 13.28 13.59 12 39 11.44 11.62 18.78 18.44 18.47 14.03 13 22 13.02 14.03 11.53 SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap app. + div. Total retum=(CA+D)/cost $ $ $ Div earnings from SBUX B. 4.94 Dividends per share of KO 100 shares Div earnings from KO Step 3: Total Return Percentage for Holding Period A SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap. app + div. Total return =(CA+D)/cost $ $ $ B. KO cost (price X 100) KO capital appreciation KO dividends (54.94 per share) KO cap app. + div. $ Total retum=(CA+D)/cost For the 6 years and 11 months you and your sister held your stocks, your stock retum was and your sister's was 3-Apr-00 1-Mar-00 1-Feb-00 3-Jan-00 1-Dec-99 1-Nov-99 1-Oct-99 1-Sep-99 2-Aug-99 1-Jul-99 1-Jun-99 3-May-99 1-Apr-99 1-Mar-99 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 1-Oct-98 1-Sep-98 3-Aug-98 1-Jul-98 1-Jun-98 1-May-98 1-Apr-98 2-Mar-98 2-Feb-98 2-Jan-98 1-Dec-97 3-Nov-97 1-Oct-97 2-Sep-97 1-Aug-97 1-Jul-97 2-Jun-97 1-May-97 1-Apr-97 3-Mar-97 3-Feb-97 2-Jan-97 2-Dec-96 1-Nov-96 1-Oct-96 3-Sep-96 1-Aug-96 1-Jul-96 3-Jun-96 1-May-96 1-Apr-96 1-Mar-96 1-Feb-96 2-Jan-96 1-Dec-95 1-Nov-95 2-Oct-95 1-Sep-95 1-Aug-95 45.93 45.62 47.09 55.82 56.41 65.18 57.00 46.61 57.61 58.34 59.72 65.82 65.40 58.98 61.22 62.60 64.22 67.15 64.62 55.12 62.14 76.81 81.58 74.64 7226 73.75 6522 61 54 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 5452 49.37 48.16 47.45 47.81 46.87 43.94 45.93 43.01 38.10 38.68 37.63 35.13 34.60 35.30 33.40 32.06 29.75 15.12 22.41 1756 16.00 12.12 13.28 13.59 12.39 11.44 11.62 18.78 18.44 18.47 14.03 13.22 13.02 14.03 11 33 10.84 9.05 7.89 10.47 13.36 12.00 12.03 1133 9.89 9.14 9.59 8.72 8.25 10.45 10.25 10.23 9.73 7.88 7.47 7.41 8.41 8.56 7.16 8.66 8.12 8.25 8.19 6.50 7.06 6.78 6.78 5.83 4.41 4.19 5.25 5.28 4.91 4.73 5 SBUX Step 4: One Year Total Return Percentage A SBUX price on Jul 02*100 SBUX price on Jul 01.100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA+D)/price lu101 B KO price on Jul 02*100 KO price on Jul 01.100 KO dividends ( 76 persh) capital appreciation KO cap. app. + div. TR = (CA+D)/price Jul01 Question 1. Which stock did better over the holding period? Question 2 Which stock did better over the last year? Question 3. Are you surprised? Problem 2: The following table shows the historical retums for large company stocks from 1980-1999. Let's find the average retum and the standard deviation of the large firm retums. Data: Yearly Historical Returns for U.S. Large Company Stocles 1980-1999. Step 1: Average Retum Calculate the historical average return for large co stocks Use the Excel function: =sverage(cell range) 100 We need to divide by 100 because the valves are in percent A In cell 116 enter: = average(C108:C127) Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Compan y Stocles 32.61% 4.97% 21.67% 22.57% 6.19% 31.85% 18.68% 5.22% 16.58% 31.75% -3.13% 30.53% 7.62% 10.07% 1.27% 37.80% 22.74% 33.43% 28.13% 21.03% Step 2: Return Distribution Calculate the variance and standard deviation of large fimm historical retums. Variance is the average of the squared deviations from the mean We calculate the deviation of each individual return from the mean (average), square those numbers, sum the squares, and then divide by the number of returns minus one. The standard deviation is the square foot of the variance It is in percentage form and is used to make comparisons. Squared Deviation Deviation Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 st dev Large Co. Average Return Return 32.61% 4.97% 21.67% 22.57% 6.19% 31.85% 18.68% 5.22% 16.58% 31.75% -3.13% 30.53% 7.62% 10.07% 1.27% 37.80% 22.74% 33.43% 28.13% 21.03% Steps for calculating standard deviation: Hint: range means a row or column of cells A Enter the 1980-1999 historical average from J116. Copy from year 1 to 20. B. Find the yearly deviation by subtracting the average return from the Lg Co Ret. Copy from year 1 to 20 12 13 14 15 16 17 18 19 20 C. Find the squared deviation by multiplying the deviation by itself Copy D. In cell F153 enter: =sum(range) (20-1) E. In cell F154 take the square root ofF153 Enter: = sqrt(F153) variance st dev Test Your Skills: Question 1: A stock had annual retums of 19 percent. -5 percent. 14 percent. 16 percent, 11 percent & 12 percent each for the past six years. What is the average return and standard deviation for this stock? Return Year 1 2 3 4 5 6 Average retum Standard deviation Question 2: A stock had annual returns of-1 percent. -5 percent. O percent, 16 percent, 25 percent, and 50 percent each for the past six years. What is the average return and standard deviation for this stock? Return Year 1 2 3 4 6 Average retum Standard deviation Stock Returns and Variance Estimating stock retums, variance and standard deviation Problem 1: You bought 100 shares of Starbucks Corp. (SBUX) 7 years ago (1 Aug '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 (excluding brokerage zes)? Assume today is July 1, 2002 and Coke paid cumulative dividends of $4.94 Data: Monthly Closing Prices From March 1995 to July 2002 for Coca-Cola and Starbucks. A Step 1: Capital Appreciation A Price you received today Price you paid Diference 100 shares In 1995 you spent $500 for 100 shares of SBUX Today. When you sold them your capital appreciation is valued at > At the same time your sister bought 100 shares of Coca Cola What was her stock's capital appreciation? Price she received today Price she paid Difference - 100 shares Today, when she sold them her capital appreciation is valued at => B. Date 1-Jul-02 3-Jun-02 1-May-02 1-Apr-02 1-Mar-02 1-Feb-02 2-Jan-02 3-Dec-01 1-Nov-01 1-Oct-01 4-Sep-01 1-Aug-01 2-Jul-01 1-Jun-01 1-May-01 2-Apr-01 1-Mar-01 1-Feb-01 2-Jan-01 1-Dec-00 1-Nov-00 2-Oct-00 1-Sep-00 1-Aug-00 3-Jul-00 1-Jun-00 1-May-00 3-Apr-00 1-Mar-00 1-Feb-00 3-Jan-00 1-Dec-99 1-Nov-99 1-Oct-99 1-Sep-99 2-Aug-99 1-Jul-99 1-Jun-99 3-May-99 1-Apr-99 1-Mar-99 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 49.43 56.00 55.33 55.30 52.07 47.02 43.41 46.78 46.59 47.33 46.31 48.11 44.08 44.48 46.67 45.48 44.46 52.02 56.90 59.78 61.44 59.06 53.93 5133 59.79 56.01 51.88 45.93 45.62 47.09 55.62 56.41 65.18 57.00 46.61 57.61 58.34 59.72 65.82 65.40 58.98 61.22 62.60 64.22 67.15 SBUX 20.31 24.85 24.28 22.82 23.13 23.01 23.77 19.05 17.72 17.12 14.94 16.87 18.04 23.00 19.52 19.35 21.22 23.81 24.97 22.12 22.78 22:34 20.03 18.31 18.75 19.09 17.00 15.12 22.41 1756 Step 2: Dividends Dividends are given 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX 16.00 B. 4.94 Dividends per share of KO * 100 shares Div ezmings from KO Step 3: Total Return Percentage for Holding Period 12.12 13.28 13.59 12 39 11.44 11.62 18.78 18.44 18.47 14.03 13 22 13.02 14.03 11.53 SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap app. + div. Total retum=(CA+D)/cost $ $ $ Div earnings from SBUX B. 4.94 Dividends per share of KO 100 shares Div earnings from KO Step 3: Total Return Percentage for Holding Period A SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap. app + div. Total return =(CA+D)/cost $ $ $ B. KO cost (price X 100) KO capital appreciation KO dividends (54.94 per share) KO cap app. + div. $ Total retum=(CA+D)/cost For the 6 years and 11 months you and your sister held your stocks, your stock retum was and your sister's was 3-Apr-00 1-Mar-00 1-Feb-00 3-Jan-00 1-Dec-99 1-Nov-99 1-Oct-99 1-Sep-99 2-Aug-99 1-Jul-99 1-Jun-99 3-May-99 1-Apr-99 1-Mar-99 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 1-Oct-98 1-Sep-98 3-Aug-98 1-Jul-98 1-Jun-98 1-May-98 1-Apr-98 2-Mar-98 2-Feb-98 2-Jan-98 1-Dec-97 3-Nov-97 1-Oct-97 2-Sep-97 1-Aug-97 1-Jul-97 2-Jun-97 1-May-97 1-Apr-97 3-Mar-97 3-Feb-97 2-Jan-97 2-Dec-96 1-Nov-96 1-Oct-96 3-Sep-96 1-Aug-96 1-Jul-96 3-Jun-96 1-May-96 1-Apr-96 1-Mar-96 1-Feb-96 2-Jan-96 1-Dec-95 1-Nov-95 2-Oct-95 1-Sep-95 1-Aug-95 45.93 45.62 47.09 55.82 56.41 65.18 57.00 46.61 57.61 58.34 59.72 65.82 65.40 58.98 61.22 62.60 64.22 67.15 64.62 55.12 62.14 76.81 81.58 74.64 7226 73.75 6522 61 54 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 5452 49.37 48.16 47.45 47.81 46.87 43.94 45.93 43.01 38.10 38.68 37.63 35.13 34.60 35.30 33.40 32.06 29.75 15.12 22.41 1756 16.00 12.12 13.28 13.59 12.39 11.44 11.62 18.78 18.44 18.47 14.03 13.22 13.02 14.03 11 33 10.84 9.05 7.89 10.47 13.36 12.00 12.03 1133 9.89 9.14 9.59 8.72 8.25 10.45 10.25 10.23 9.73 7.88 7.47 7.41 8.41 8.56 7.16 8.66 8.12 8.25 8.19 6.50 7.06 6.78 6.78 5.83 4.41 4.19 5.25 5.28 4.91 4.73 5 SBUX Step 4: One Year Total Return Percentage A SBUX price on Jul 02*100 SBUX price on Jul 01.100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA+D)/price lu101 B KO price on Jul 02*100 KO price on Jul 01.100 KO dividends ( 76 persh) capital appreciation KO cap. app. + div. TR = (CA+D)/price Jul01 Question 1. Which stock did better over the holding period? Question 2 Which stock did better over the last year? Question 3. Are you surprised? Problem 2: The following table shows the historical retums for large company stocks from 1980-1999. Let's find the average retum and the standard deviation of the large firm retums. Data: Yearly Historical Returns for U.S. Large Company Stocles 1980-1999. Step 1: Average Retum Calculate the historical average return for large co stocks Use the Excel function: =sverage(cell range) 100 We need to divide by 100 because the valves are in percent A In cell 116 enter: = average(C108:C127) Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Compan y Stocles 32.61% 4.97% 21.67% 22.57% 6.19% 31.85% 18.68% 5.22% 16.58% 31.75% -3.13% 30.53% 7.62% 10.07% 1.27% 37.80% 22.74% 33.43% 28.13% 21.03% Step 2: Return Distribution Calculate the variance and standard deviation of large fimm historical retums. Variance is the average of the squared deviations from the mean We calculate the deviation of each individual return from the mean (average), square those numbers, sum the squares, and then divide by the number of returns minus one. The standard deviation is the square foot of the variance It is in percentage form and is used to make comparisons. Squared Deviation Deviation Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 st dev Large Co. Average Return Return 32.61% 4.97% 21.67% 22.57% 6.19% 31.85% 18.68% 5.22% 16.58% 31.75% -3.13% 30.53% 7.62% 10.07% 1.27% 37.80% 22.74% 33.43% 28.13% 21.03% Steps for calculating standard deviation: Hint: range means a row or column of cells A Enter the 1980-1999 historical average from J116. Copy from year 1 to 20. B. Find the yearly deviation by subtracting the average return from the Lg Co Ret. Copy from year 1 to 20 12 13 14 15 16 17 18 19 20 C. Find the squared deviation by multiplying the deviation by itself Copy D. In cell F153 enter: =sum(range) (20-1) E. In cell F154 take the square root ofF153 Enter: = sqrt(F153) variance st dev Test Your Skills: Question 1: A stock had annual retums of 19 percent. -5 percent. 14 percent. 16 percent, 11 percent & 12 percent each for the past six years. What is the average return and standard deviation for this stock? Return Year 1 2 3 4 5 6 Average retum Standard deviation Question 2: A stock had annual returns of-1 percent. -5 percent. O percent, 16 percent, 25 percent, and 50 percent each for the past six years. What is the average return and standard deviation for this stock? Return Year 1 2 3 4 6 Average retum Standard deviation
