Question

This assignment will require you to analyze time series of monthly returns. Start by retrieving MONTHLY data for the period of 08/01/2015 – 08/31/2019 from Yahoo website for −
S&P 500 Index (ticker: ^GSPC)
− General Electric Company (ticker: GE)
− Chevron Corporation (ticker: CVX)
− Intel Corporation (ticker: INTC)
− Tesla, Inc. (ticker: TSLA)

Instructions for downloading the data from Yahoo! Website (https://finance.yahoo.com/): To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click on “Historical Data”. Enter “Time Period” as given above. For “Frequency”, make sure Monthly is selected and then click on “Apply”. Click on “Download Data”.

Saving your final file:
Keep only Date and Adj Close columns for each company. Put all five sets of data in one excel file to do further analysis.

Very important: Save your final file as a .xls or .xlsx file before you start the computations. Comma delimited (.csv) files do not retain the formulae and cell references after closing the file. You will receive a grade of zero if your file does not contain cell references and formulae that show how you arrived at the various answers.

Calculating Returns:

Use the ‘Adj Close’ column to obtain returns for each period. Remember that the Adjusted Close column has already adjusted the prices for dividends and stock splits so you do not have to adjust for it again. Just use the adjusted close column to obtain the return R for each month t as: Rt = Adj Closet/Adj Closet-1 – 1

MINICASE 1

Solve for the following: [20 points]
A. Calculate the monthly returns for 08/01/2015 – 08/31/2019 period for
(i) S&P 500
(ii) GE
(iii) Chevron
(iv) Intel
(v) Tesla

[15 points] B. What is the average monthly return and standard deviation of returns for 08/01/2015 – 08/31/2019 period for
(i) S&P 500
(ii) GE
(iii) Chevron
(iv) Intel
(v) Tesla

[25 points] C. Calculate the correlation between:
(i) GE and Chevron
(ii) GE and Intel
(iii) GE and Tesla
(iv) Chevron and Tesla
(v) Chevron and Intel
(vi) Intel and Tesla
(vii) Explain your findings

[20 points] D. Calculate the stock betas for the 08/01/2015 – 08/31/2019 period for:
(i) GE
ii) Chevron
(iii) Intel
(iv) Tesla
(v) Comment on your findings.

[60 points]
E. Form three portfolio sand then answer the following questions:
 Portfolio 1: 50% in GE and 50% in Chevron
 Portfolio 2: 1/3rd in GE, 1/3rd in Chevron and 1/3rd in Intel
 Portfolio 3: 25% in GE, 25% in Chevron, 25% in Intel, and 25% in Tesla

(i) What is the average return and standard deviation of each portfolio?
(ii) What happens to your portfolio return and standard deviation as you add more securities in your portfolio?
iii) What could be the reason behind the pattern that you observe?

*SHOW ALL CALCULATIONS

Answer 1

(a)

Monthly return = (current month price - previous month price) / previous month price

(b)

Average and standard deviation are computed using AVERAGE and STDEV functions in Excel.

(c)

Correlation is computed using CORREL function in Excel.

(d)

Beta of each stock = Covariance (stock, GSPC) / Variance (GSPC).

Covariance and Variance are computed using COVAR and VAR functions in Excel.

А C E F Н К Monthly Monthly Return CVX Monthly Monthly Return Monthly Return GE CVX Return INTC Return 1 Date GSPC GSPC INTC TSLA GE TSLA 1972.18 22.03 01-08-15 67.98 25.32 249.06 6.48% 01-09-15 1920.03 -2.64% 21.36 -3.04% 67.05 -1.38% 26.96 248.40 -0.26% 8.30 % 77.25 01-10-15 2079.36 24.71 15.70% 15.21% 30.29 12.34% 206.93 -16.69% 11.27 % 5 01-11-15 31.10 2.69 % 2080.41 0.05% 25.58 3.53% 77.62 0.48% 230.26 4.04 % -0.29% 4.23% 6 01-12-15 2043.94 -1.75% 26.61 77.40 31.04 -0.22% 240.01 25.05 7 01-01-16 1940.24 -5.07% -5.88% 74.40 -3.88% 27.95 -9.96% 191.20 -20.34% 71.79 26.66 8 01-02-16 1932.23 -0.41% 25.08 0.14% -3.50% -4.61% 191.93 0.38% 9 01-03-16 10 01-04-16 11 01-05-16 6.60 % 0.27 % 10.29 % 27.58 2059.74 9.97% 83.12 15.78% 29.40 229.77 19.72% -3.27 % 2065.30 26.68 89.03 7.11% 27.52 -6.40% 240.76 4.78% -7.28 % 26.23 -1.69 % 2096.95 1.53% 88.00 -1.15% 28.71 4.33% 223.23 0.09 % 12 01-06-16 2098.86 27.31 4.13% 92.30 4.89% 30.07 4.73% 212.28 -4.91 % 13 01-07-16 2173.60 -2.24 % -1.85 % 3.56% 27.22 -0.33% 90.23 31.95 6.28% 234.79 10.60% 14 01-08-16 2170.95 -0.12% 27.31 0.32% 88.56 32.90 2.95% 212.01 -9.70% 15 01-09-16 25.89 -5.19% 204.03 2168.27 -0.12% 91.57 3.40% 34.87 5.98% -3.76% 16 01-10-16 17 01-11-16 18 01-12-16 2126.15 -1.94% 25.64 -0.99% 93.20 1.78% 32.21 -7.63% 197.73 -3.09% 6.50% -0.49 % 2198.81 3.42% 27.10 5.70% 99.26 32.05 189.40 -4.21% 12.82 % 2238.83 1.82% 27.84 2.73% 105.77 6.56% 33.75 5.32% 213.69 -5.31 % 17.90 % 19 01-01-17 20 01-02-17 21 01-03-17 22 01-04-17 23 01-05-17 24 01-06-17 25 01-07-17 26 01-08-17 27 01-09-17 100.07 2278.87 1.79% 26.36 -5.40% 34.26 1.52% 251.93 -1.68 % 2363.64 3.72 % 26.46 0.37% 101.10 1.03% 33.69 249.99 -0.77% 11.32 % 2362.72 -0.04% 26.66 0.76% 97.41 -3.65% 33.81 0.35% 278.30 2384.20 0.22 % -0.11 % -5.87 % 0.91% 25.94 -2.72% 96.81 -0.62% 33.88 314.07 12.85% 2411.80 1.16% 24.50 -5.55% 93.88 -3.02% 33.84 341.01 8.58% 6.04 % 2423.41 31.86 0.48% 24.17 -1.35% 95.62 1.85% 361.61 2470.30 1.93% 23.31 -3.55% 100.07 4.66% 33.49 5.13% 323.47-10.55% -4.14 % -1.44 % 22.34 98.63 33.11 10.03% 2471.65 0.05% -1.13% 355.90 -4.16 % 2519.36 1.93% 22.01 -1.51% 108.77 10.28% 36.23 9.40% 341.10 28 01-10-17 2575.26 2.22% 18.53 -15.79% 107.28 -1.37% 43.28 19.46% 331.53 -2.81% -9.28 % 29 01-11-17 0.37 % 2584.84 16.81 110.15 2.67% 42.66 -1.43% 308.85 -6.84%

A В Е Н J К Monthly Monthly Monthly Return INTC INTC Monthly Return TSLA TSLA Monthly Return GE CVX -1.51% 108.77 Return Return GSPC Date GSPC GE CVX 27 01-09-17 2519.36 1.93% 22.01 10.28% 36.23 9.40% 341.10 -4.16% 28 01-10-17 2575.26 2.22% 18.53 -15.79% 107.28 -1.37% 43.28 19.46% 331.53 -2.81% 0.37% 16.81 -9.28% 110.15 -1.43 % 308.85 29 01-11-17 2584.84 2.67% 42.66 -6.84% 6.20% 44.17 0.81 13.80% 30 01-12-17 2673.61 3.43% 16.04 -4.59% 116.97 -6.04% 3.55% 311.35 0.13 46.07 -10.71% 47.17 2.91% 50.18 9.71 49.73 -0.65% 53.18 31 01-01-18 2823.81 5.62% 15.07 117.12 4.29% 354.31 32 01-02-18 2713.83 -3.89% 13.15 -12.74% 104.58 2.39% 343.06 -3.18% 01-03-18 2640.87 -2.69% 12.67 -3.67% 107.62 6.38% 266.13 33 -22.42% 0.27% 13.22 2.16% 13.23 10.43% 34 01-04-18 2648.05 4.38% 118.06 -0.88% 293.90 0.07% 117.30 -3.12% 35 01-05-18 2705.27 6.94% 284.73 36 01-06-18 2718.37 0.48% 12.79 -3.34% 120.35 2.60% 48.17 -9.43% 342.95 20.45% 1.04% 120.20 -5.06 112.76 3.60% 12.92 37 01-07-18 2816.29 -0.13% 46.61 -3.24% 298.14 -13.07% 1.18 38 01-08-18 2901.52 3.03% 12.27 -6.19% 46.93 0.69% 301.66 4.21% 46.10 39 01-09-18 2913.98 0.43% 10.71 -12.75% 117.52 -1.76% 264.77 -12.23% -0.87% 337.32 5.18 350.48 -4.23 % 332.80 40 01-10-18 2711. 74 -6.94% 9.67 -9.69% 107.30 -8.69% 45.70 27.40% 3.90 1.79 6.53 48.07 41 01-11-18 2760.17 7.18 -25.74% 114.31 0.93% 105.57 42 01-12-18 2506.85 -9.18% 7.25 -7.64% 46.04 -5.04% 7,87% 43 01-01-19 2704.10 9.74 34.40% 111.26 5.39% 46.23 0.40% 307.02 -7.75% 2.97% 10.36 01-02-19 2784.49 44 6.35% 116.04 4.30% 51.96 12.39% 319.88 4.19% 4.05% 53.02 -2.53% 50.39 -5.17% 43.48 01-03-19 2834.40 2.04% 279.86 45 1.79% 9.96 -3.85% 120. 74 12.51% 3.93% 10.15 01-04-19 2945.83 1.91 117.68 -7.18% 111.59 46 -4.95% 238.69 -14.71% 47 01-05-19 2752.06 -6.58% 9.42 -13.71% 185.16 -22.43% 9.36% 223.46 48 01-06-19 2941.76 6.89% 10.48 11.23% 123.17 10.38% 47.55 20.68% -0.38% 121.86 8.12% -6.62% 49 01-07-19 2980.38 1.31% 10.44 -1.07% 50.21 5.60% 241.61 -6.21 225.61 -21.05% 116.52 50 01-08-19 2926.46 -1.81% 8.24 -4.38% 47.09 51 Average 52 Std. Dev. 0.89% -1.62% 1.28% 1.50% 0.48% 3.54% 9.11% 5.71% 6.38% 11.83% 53 Correl GE CVX INTC TSLA 54 0.4200 55 0.1407 0.3930 0.0294 0.1462 56 0.0343 57 58 0.86 59 Beta 1.18 0.98 0.47 Co

A В C П E F G Monthly Return - Monthly Return GE (D33-D32) /D32 Monthly Return CVX (F33- F32) F32 1 Date GSPC GSPC GE CVX 33 43160 2640.870117 = (B33-B32)/B32 12.669376 107.617081 (B34-B33) B33 2648.050049 118.063606 34 43191 13.223896 =(D34-D33)ID33 (F34-F33)F33 (F35- F34)IF34 35 43221 2705.27002 = (B35-B34)IB34 13.233294 = (D35-D34)ID34 117.299217 36 43252 12.791558 2718.370117 =(B36-B35)IB35 (D36-D35)ID35 120.349632 (F36-F35F35 (D37-D36) D36 37 43282 2816.290039 - (В37-в36)в36 12.924053 120.197319 = (F37-F36)IF36 (F38-F37 / F37 38 43313 2901.52002 (B38-B37)B37 12.269791 =(D38-D37)HD37 112.762932 39 43344 2913.97998 = (B39-B38)B38 10.70525 =(D39-D38HD38 117.515182 = (F39-F38)F38 (F40-F39) F39 = (F41-F40)IF40 =(D40-D39)/D39 = (D41-D40)/D40 40 43374 2711.73999 = (B40-B39)B39 = (B41-B40)/B40 (B42-B41)/B41 (B43-B42) B42 = (B44-B43)B43 9.668162 107.299393 41 43405 2760.169922 7.179327 114.305328 7.246335 42 43435 2506.850098 =(D42-D41)D41 105.572632 (F42-F41)F41 9.73898 43 43466 2704.100098 (D43-D42)D42 111.259331 (F43- F42)} F42 44 43497 2784.48999 10.357828 (044-043)D43 116.043526 (F44-F43)F43 45 43525 2834.399902 (B45-B44)B44 9.959066 =(D45-D44)ID44 120.738686 (F45-F44)IF44 (D46-D45) D45 46 43556 2945.830078 (B46-B45)B45 10.149249 117.680527 (F46-F45)IF45 47 43586 48 43617 111.593597 2752.060059 (B47-B46)B46 9.420737 =(D47-D46)D46 (F47-F46)F46 (F48-F47) F47 (B48-B47B47 2941.76001 10.478576 = (D48-D47)D47 123.17379 121.857323 49 43647 2980.379883 = (B49-B48)B48 10.438715 =(D49-048)HD48 (F49-F48)IF48 (F50-F49) F49 -AVERAGE(G3:G50) (D50-D49)D49 -AVERAGE(E3:E50) =STDEV(E3: E50) 50 43678 2926.459961 = (B50-B49)IB49 8.241091 116.522163 =AVERAGE(C3: C50) 51 Average 52 Std. Dev 53 Correl -STDEV (C3 : C50) -STDEV (G3 : G50) =CORREL(E3:E50,G3:G50) 54 55 =CORREL(E3: E50,13:150) =CORRELIG3:G50,13:150) 56 CORREL(E3:E50,K3:K50) =CORRELIG3:G50,K3:K50) 57 58 COVAR(G3:G50,$C$3: $C$50 ) VAR($C$3: $C$50) 59 Beta COVAR(E3:E50,$C$3: $C$50)}VAR($C$3: $C$50) 60

A В C Е Н J К L Monthly Return GSPC (B33-B32) B32 (B34-B33IB33 Monthly Return GE Monthly Return - Monthly Return - TSLA =(J33-J32) J32 =(J34-J33)J33 INTC Monthly Return INTC - (Н33-Н32)/Н32 (H34-H33H33 CVX 107.617081 TSLA 266.130005 1 Date 33 43160 GSPC GE CVX 2640.870117 12.669376 =(D33-D32)D32 =(F33-F32F32 50.17598 2648.050049 (D34-D33ID33 49.732792 34 43191 13.223896 118.063606 = (F34-F33)IF33 293.899994 117.299217 53.181911 35 43221 2705.27002 = (B35-B34)IB34 13.233294 = (D35-D34)ID34 (F35-F34)F34 (H35-H34H34 284.730011 =(J35-J34J34 (F36-F35) F35 (H36-H35H35 (H37-H36)H36 36 43252 2718.370117 = (B36-B35)B35 12.791558 =(D36-035)D35 120.349632 48.169029 342.950012 =(J36-J35J35 3 [В37-вз6)/ВЗ6 37 43282 12.924053 (D37-D36 )/ D36 (D38-D37)D37 2816.290039 120.197319 (F37-F36F36 46.608944 298.140015 =[J37-J36)J36 2901.52002 38 43313 = (B38-B37B37 12.269791 112.762932 = [F38-F37IF37 46.928711 = (H38- H37) H37 301.660004 =(J38-J37J37 264.769989 337.320007 39 43344 2913.97998 = (B39-B38)B38 10.70525 =(D39-D38HD38 117.515182 (F39-F38)F38 46.10273 =(H39-H38)lH38 (J39-J38J38 9.668162 7.179327 45.703022 40 43374 41 43405 2711.73999 (B40-B39)IB39 =(D40-039)HD39 107.299393 (F40-F39)IF39 - (Н40-нз9)н39 =(J40-J39)J39 2760.169922 =(H41-H40)H40 =(H42-H41)H41 - (J41-J40)J40 = (B41-B40HB40 (D41-D40)D40 114.305328 (F41-F40)IF40 48.072018 350.480011 =(B42-B41)/B41 7.246335 46.039696 42 43435 2506.850098 =(D42-D41ID41 105.572632 (F42-F41F41 332.799988 (J42-J41)J41 307.019989 43 43466 2704.100098 = (B43-B42)B42 9.73898 = (D43-D42)/D42 111.259331 (F43-F42)/F42 46.226089 (H43-H42)H42 (J43-J42)J42 116.043526 44 43497 2784.48999 = (B44-B43)IB43 10.357828 (044-043)D43 = [F44-F43)IF43 51.955296 =(H44-H43)H43 319.880005 (J44-J43J43 [F45-F44)I F44 9.959066 279.859985 238.690002 =[J45-J44)J44 45 43525 2834.399902 = (B45-B44B44 (D45-D44)HD44 120.738686 53.01519 (H45-H44)H44 = (H46-H45)H45 = (H47-H46)H46 (H48-H47 )H47 46 43556 2945.830078 117.680527 =(B46-B45B45 10.149249 (D46-D45)D45 = [F46-F45IF45 50.389111 =(J46-J45)J45 9.420737 10.478576 47 43586 43.478382 2752.060059 (B47-B46)B46 =(D47-046)D46 111.593597 (F47-F46)F46 185.160004 [J47-J46)J46 223.460007 241.610001 48 43617 2941.76001 (B48-B47)HB47 = (D48-D47)D47 123.17379 (F48-F47F47 47.548962 (J48-J47J47 (D49-D48) D48 (F49-F48) F48 =(F50-F49) F49 -AVERAGE(G3:G50] -STDEV(G3: G50) 49 43647 2980.379883 = (B49-B48)B48 10.438715 121.857323 50.210991 (H49-H48)H48 E(J4g-J48)J48 8.241091 116.522163 47.092049 =[J50-J49)J49 -AVERAGE(K3:K50 =STDEV(K3 : K50) 50 43678 51 Average 52 Std. Dev 53 Correl 2926.459961 (B50-B49)HB49 -AVERAGE(C3:C50) =(D50-049)HD49 (H50-H49)H49 -AVERAGE(13:150) 225.610001 -AVERAGE(E3:E =STDEV(E3:E50 =STDEV(C3 : C50) =STDEV(13:150) GE =CORREL(E3:E50, =CORREL(E3:E50, CVX 54 55 =CORREL(G3:G50,13:150 INTC 56 CORREL(E3:E50, CORRELIG3:G50,K3:K =CORREL(13:150,K3:K50) TSLA 57 58 COVAR(E3:E50,$ =COVAR(K3: K50,$C$3 59 Beta =COVARIG3:G50,$C$3: =COVAR(3:150,$C$3: C$ 50 ) / VAR($C$3: $C$50) 60