In this spreadsheet you will find the price of 3 stocks - these are data from...

Transcribed Image Text:

In this spreadsheet you will find the price of 3 stocks - these are data from the NYSE. a) For each stock compute the rate of return r = ln(P, /P) where Pit is the price of stock i at time t. Be i,t-1 sure to place the stock prices in the correct order to calculate the returns. (4 marks) b) Compute the mean and standard deviation for the stock returns of each asset using the formula. Comment on your results. (6 marks) Where mean is the average I (avg) and the standard deviation is. ( ((r-r(avg) c) Assume you would like to invest in a portfolio composed by the 3 assets in the spreadsheet. Further assume that you would like to invest the same amount of money in each asset. Compute the expected return and the risk of your portfolio. Comment on your results. (10 marks) B E 1 Date AA GE W 2345678901221221222222222222352333858SGE 05-Jan-04 38.24 30.25 92.9 06-Jan-04 37.95 30.05 92.91 07-Jan-04 37.67 30.35 92.63 08-Jan-04 38.12 30.89 92.89 09-Jan-04 36.73 30.46 91.06 12-Jan-04 35.47 30.74 91.4 13-Jan-04 34.66 30.28 89.55 14-Jan-04 35.46 30.65 90.16 15-Jan-04 34.98 30.65 93.87 16-Jan-04 34.55 31.95 95.17 20-Jan-04 35.72 31.96 96.94 21-Jan-04 35.79 32.43 97.54 22-Jan-04 35.92 32.11 97.35 23-Jan-04 34.91 31.81 97.74 26-Jan-04 34.86 32.7 99.69 27-Jan-04 34.77 32.75 98.64 28-Jan-04 33.31 32.34 97.22 29-Jan-04 32.84 32.6 97.85 30-Jan-04 33.7 32.22 99.07 02-Feb-04 33.01 32.22 99.23 03-Feb-04 33.63 31.81 99.84 04-Feb-04 33.67 31.79 100.03 24 05-Feb-04 34.04 32.11 98.7 06-Feb-04 34.73 31.79 98.94 09-Feb-04 34.61 31.51 98.95 10-Feb-04 35.2 31.13 99.61 11-Feb-04 37.03 31.69 99.96 12-Feb-04 36.55 31.48 99.3 13-Feb-04 36.66 31.34 99.71 17-Feb-04 37.39 31.66 99.37 18-Feb-04 37.43 31.37 98.42 19-Feb-04 37.38 31.23 97.8 20-Feb-04 36.48 31.33 97.31 23-Feb-04 36.82 32.02 95.96 24-Feb-04 36.9 31.88 96.79 25-Feb-04 36.46 31.58 96.54 26-Feb-04 36.81 31.49 96.79 27-Feb-04 37.11 31.34 96.5 01-Mar-04 38.03 31.6 97.04 02-Mar-04 37.35 31.31 96.82 Stocks CAPM Sheet3 + Ready Accessibility: Good to go In this spreadsheet you will find the price of 3 stocks - these are data from the NYSE. a) For each stock compute the rate of return r = ln(P, /P) where Pit is the price of stock i at time t. Be i,t-1 sure to place the stock prices in the correct order to calculate the returns. (4 marks) b) Compute the mean and standard deviation for the stock returns of each asset using the formula. Comment on your results. (6 marks) Where mean is the average I (avg) and the standard deviation is. ( ((r-r(avg) c) Assume you would like to invest in a portfolio composed by the 3 assets in the spreadsheet. Further assume that you would like to invest the same amount of money in each asset. Compute the expected return and the risk of your portfolio. Comment on your results. (10 marks) B E 1 Date AA GE W 2345678901221221222222222222352333858SGE 05-Jan-04 38.24 30.25 92.9 06-Jan-04 37.95 30.05 92.91 07-Jan-04 37.67 30.35 92.63 08-Jan-04 38.12 30.89 92.89 09-Jan-04 36.73 30.46 91.06 12-Jan-04 35.47 30.74 91.4 13-Jan-04 34.66 30.28 89.55 14-Jan-04 35.46 30.65 90.16 15-Jan-04 34.98 30.65 93.87 16-Jan-04 34.55 31.95 95.17 20-Jan-04 35.72 31.96 96.94 21-Jan-04 35.79 32.43 97.54 22-Jan-04 35.92 32.11 97.35 23-Jan-04 34.91 31.81 97.74 26-Jan-04 34.86 32.7 99.69 27-Jan-04 34.77 32.75 98.64 28-Jan-04 33.31 32.34 97.22 29-Jan-04 32.84 32.6 97.85 30-Jan-04 33.7 32.22 99.07 02-Feb-04 33.01 32.22 99.23 03-Feb-04 33.63 31.81 99.84 04-Feb-04 33.67 31.79 100.03 24 05-Feb-04 34.04 32.11 98.7 06-Feb-04 34.73 31.79 98.94 09-Feb-04 34.61 31.51 98.95 10-Feb-04 35.2 31.13 99.61 11-Feb-04 37.03 31.69 99.96 12-Feb-04 36.55 31.48 99.3 13-Feb-04 36.66 31.34 99.71 17-Feb-04 37.39 31.66 99.37 18-Feb-04 37.43 31.37 98.42 19-Feb-04 37.38 31.23 97.8 20-Feb-04 36.48 31.33 97.31 23-Feb-04 36.82 32.02 95.96 24-Feb-04 36.9 31.88 96.79 25-Feb-04 36.46 31.58 96.54 26-Feb-04 36.81 31.49 96.79 27-Feb-04 37.11 31.34 96.5 01-Mar-04 38.03 31.6 97.04 02-Mar-04 37.35 31.31 96.82 Stocks CAPM Sheet3 + Ready Accessibility: Good to go