PLEASE WORK IT ON EXCEL AND SHOW ME HOW ?
A B C D E F G H I J K L 6 7 8 Estimating stock returns, 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 10 your returns compare (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 A Step 1: Capital Appreciation A. Price you received today Price you paid Difference * 100 shares In 1995 you spent $500 for 100 shares of SBUX. Today, when you sold them, your capital appreciation is valued at ==> B. At the same time your sister bought 100 shares of Coca Cola. What was her stock's capital appreciation? 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 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 KO 49.43 56.00 55.35 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 51.33 59.79 56.01 51.88 45.93 45.62 47.09 55.62 56.41 65.18 57.00 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 17.56 16.00 12.12 13.28 13.59 Price she received today Price she paid Difference * 100 shares Today, when she sold them, her capital appreciation is valued at ==> Step 2: Dividends Dividends are given. A. 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX $ B. 4.94 Dividends per share of KO * 100 shares Div earnings from KO Std dev COV_corr + A B D E F G H I J F Step 2: Dividends Dividends are given. 53.93 51.33 59.79 56.01 51.88 45.93 A. 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX S 45.62 B. 4.94 Dividends per share of KO * 100 shares Div earnings from KO Step 3: Total Return Percentage for Holding Period A. 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap. app. + div. Total return = (CA + D) / cost 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 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 20.03 18.31 18.75 19.09 17.00 15.12 22.41 17.56 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.53 10.84 9.05 7.89 10.47 13.36 12.00 12.03 11.33 B. 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 64.62 55.12 62.14 76.81 81.58 74.64 72.26 73.75 65.22 61.54 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 54.52 49.57 KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return - (CA + D) / cost S S $ For the 6 years and 11 months you and your sister held your stocks, your stock return was and your sister's was => 9.89 9.14 9.59 Step 4: One Year Total Return Percentage A. 8.72 8.25 10.45 10.25 10.23 9.73 7.88 7.47 7.41 8.41 8.56 7.16 SBUX price on Jul 02*100 SBUX price on Jul 01*100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA + D) / price Jul01 B. KO price on Jul 02*100 Std dev COV_corr + E133 fx A E F J OT 01.22 D 13.22 13.02 14.03 G H I DUA cap. app. I uiv. Total return = (CA + D) / cost 11.53 B. 62.60 64.22 67.15 64.62 55.12 62.14 76.81 81.58 74.64 72.26 73.75 65.22 KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return = (CA+ D) / cost $ $ $ For the 6 years and 11 months you and your sister held your stocks, your stock return was sister's was and your => 61.54 Step 4: One Year Total Return Percentage A 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 B ITCU-77 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 10.84 9.05 7.89 10.47 13.36 12.00 12.03 11.33 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 SBUX price on Jul 02*100 SBUX price on Jul 01*100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA+ D) / price Jul01 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 54.52 49.57 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 KO B. KO price on Jul 02*100 KO price on Jul 01*100 KO dividends (.76 per sh) 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: A B C D E F G H I J K L 6 7 8 Estimating stock returns, 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 10 your returns compare (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 A Step 1: Capital Appreciation A. Price you received today Price you paid Difference * 100 shares In 1995 you spent $500 for 100 shares of SBUX. Today, when you sold them, your capital appreciation is valued at ==> B. At the same time your sister bought 100 shares of Coca Cola. What was her stock's capital appreciation? 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 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 KO 49.43 56.00 55.35 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 51.33 59.79 56.01 51.88 45.93 45.62 47.09 55.62 56.41 65.18 57.00 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 17.56 16.00 12.12 13.28 13.59 Price she received today Price she paid Difference * 100 shares Today, when she sold them, her capital appreciation is valued at ==> Step 2: Dividends Dividends are given. A. 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX $ B. 4.94 Dividends per share of KO * 100 shares Div earnings from KO Std dev COV_corr + A B D E F G H I J F Step 2: Dividends Dividends are given. 53.93 51.33 59.79 56.01 51.88 45.93 A. 0 Dividends per share of SBUX * 100 shares Div earnings from SBUX S 45.62 B. 4.94 Dividends per share of KO * 100 shares Div earnings from KO Step 3: Total Return Percentage for Holding Period A. 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap. app. + div. Total return = (CA + D) / cost 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 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 20.03 18.31 18.75 19.09 17.00 15.12 22.41 17.56 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.53 10.84 9.05 7.89 10.47 13.36 12.00 12.03 11.33 B. 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 64.62 55.12 62.14 76.81 81.58 74.64 72.26 73.75 65.22 61.54 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 54.52 49.57 KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return - (CA + D) / cost S S $ For the 6 years and 11 months you and your sister held your stocks, your stock return was and your sister's was => 9.89 9.14 9.59 Step 4: One Year Total Return Percentage A. 8.72 8.25 10.45 10.25 10.23 9.73 7.88 7.47 7.41 8.41 8.56 7.16 SBUX price on Jul 02*100 SBUX price on Jul 01*100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA + D) / price Jul01 B. KO price on Jul 02*100 Std dev COV_corr + E133 fx A E F J OT 01.22 D 13.22 13.02 14.03 G H I DUA cap. app. I uiv. Total return = (CA + D) / cost 11.53 B. 62.60 64.22 67.15 64.62 55.12 62.14 76.81 81.58 74.64 72.26 73.75 65.22 KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return = (CA+ D) / cost $ $ $ For the 6 years and 11 months you and your sister held your stocks, your stock return was sister's was and your => 61.54 Step 4: One Year Total Return Percentage A 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 B ITCU-77 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 10.84 9.05 7.89 10.47 13.36 12.00 12.03 11.33 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 SBUX price on Jul 02*100 SBUX price on Jul 01*100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA+ D) / price Jul01 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 54.52 49.57 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 KO B. KO price on Jul 02*100 KO price on Jul 01*100 KO dividends (.76 per sh) 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
