I need part F. I have the answers for the one before. It needs to be done in excel
.
A. Month 3 4 6 7 Security A 1 Time 2 8.82% 9.29% -4.41% 1 5.11% - 13.51% 5.45% 5 5.75% 8.33% 0.55% B -1.08% 16.41% 7.61% 9.24% 14.74% 5.11% -4.69% -4.65% 3.21% 1 1 2 $ 3 Security A Security B Security C Price Dividend Price Dividend Price Dividend $ 8.50 $ 35.00 $ 178.00 8.80 $ 0.45 $ 37.00 $ 1.25 $ 165.00 $ 5.15 $ 9.25 $ 32.00 $ 174.00 8.70 $ 0.45 $ 36.00 $ 1.25 $ 182.00 $ 5.25 $ 9.20 $ 39.00 $ 183.00 9.60 $ 0.45 $ 43.00 $ 1.75 $ 187.00 $ 5.35 9.15 41.00 $ 193.00 B. 4 $ Security 1 5 Average monthly return 3.86% 5.10% 2.92% 1 B 6 S 1 7 $ C. Security A Standard deviation 0.051019 2 2 2 B 0.107265 0.039305 D. Covariance Security B Correlation Security A 2 A A 0.26% 0.14% -0.09% -0.09% -0.02% 0.15% A B 100.00% 25.25% -42.86% 25.25% 100.00% -5.59% -42.86% -5.59% 100.00% 0.14% 1.15% -0.02% 2 B B 2 E. SD 2 i. 2 ii. Avg. Ret 4.48% 3.39% 4.01% 3.96% 7.91% 4.52% 7.33% 6.59% 2 iii. 2 iv. Below is actual price and dividend data for three companies for each of seven months. Price $ is Time 1 2 3 4 5 6 7 $ S $ $ $ Security A Security B Security C Dividend Price Dividend Price Dividend 8.50 $ 35.00 $178.00 8.80 $ 0.45 $ 37.00 $ 1.25 $165.00 $ 5.15 9.25 $ 32.00 $174.00 8.70 $ 0.45 $36.00 $ 1.25 $182.00 $ 5.25 9.20 $39.00 $183.00 9.60 $ 0.45 $ 43.00 $ 1.75 $187.00 $ 5.35 9.15 $ 41.00 $ 193.00 A dividend entry on the same line as a price indicates that the return between that time period and the previous period consisted of a capital gain (or loss) and the receipt of the dividend. Securities A, B and C can be referred to Securities 1, 2 and 3 respectively. For the purpose of this excercise only, please do not annualize your estimates. A. (3pts) Compute the rate of return for each company for each month (use simple return formula). B. (3pts) Compute the average rate of return for each company. C. (6pts) Compute the standard deviation of the rate of return for each company (use population variance formula. i.e. divide by N, not (N-1) ). D. (6pts) Compute the covariance and correlation coefficients between all possible pairs of securities (again, use population formula). E. (8pts) Compute the average return and standard deviation for the following portfolios: i. A+B ii. WA+C iii. 1 B + C iv. 1/3 A +1/3 B + 1/3 C F. (15 pts) Assuming short selling is not allowed: i. (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk. ii. (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2 iii. (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient. iv. (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1. 115pts Assuming short selling is allowed. F. Weights SD E[r] 2 2 2 Security pair i. A&B i. A&C i. B&C 2 1 Security pair ii. A&B iii. A&B graph goes somewhere here 2 1 graph goes somewhere here ii. A&C iii. A&C 2 1 ii. B&C iii. B&C graph goes somewhere here F. i. (15pts) Assuming short selling is not allowed: (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk. ii. (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2 iii. (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient. iv. (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1. G 1150te Assuming short selling is allowed A. Month 3 4 6 7 Security A 1 Time 2 8.82% 9.29% -4.41% 1 5.11% - 13.51% 5.45% 5 5.75% 8.33% 0.55% B -1.08% 16.41% 7.61% 9.24% 14.74% 5.11% -4.69% -4.65% 3.21% 1 1 2 $ 3 Security A Security B Security C Price Dividend Price Dividend Price Dividend $ 8.50 $ 35.00 $ 178.00 8.80 $ 0.45 $ 37.00 $ 1.25 $ 165.00 $ 5.15 $ 9.25 $ 32.00 $ 174.00 8.70 $ 0.45 $ 36.00 $ 1.25 $ 182.00 $ 5.25 $ 9.20 $ 39.00 $ 183.00 9.60 $ 0.45 $ 43.00 $ 1.75 $ 187.00 $ 5.35 9.15 41.00 $ 193.00 B. 4 $ Security 1 5 Average monthly return 3.86% 5.10% 2.92% 1 B 6 S 1 7 $ C. Security A Standard deviation 0.051019 2 2 2 B 0.107265 0.039305 D. Covariance Security B Correlation Security A 2 A A 0.26% 0.14% -0.09% -0.09% -0.02% 0.15% A B 100.00% 25.25% -42.86% 25.25% 100.00% -5.59% -42.86% -5.59% 100.00% 0.14% 1.15% -0.02% 2 B B 2 E. SD 2 i. 2 ii. Avg. Ret 4.48% 3.39% 4.01% 3.96% 7.91% 4.52% 7.33% 6.59% 2 iii. 2 iv. Below is actual price and dividend data for three companies for each of seven months. Price $ is Time 1 2 3 4 5 6 7 $ S $ $ $ Security A Security B Security C Dividend Price Dividend Price Dividend 8.50 $ 35.00 $178.00 8.80 $ 0.45 $ 37.00 $ 1.25 $165.00 $ 5.15 9.25 $ 32.00 $174.00 8.70 $ 0.45 $36.00 $ 1.25 $182.00 $ 5.25 9.20 $39.00 $183.00 9.60 $ 0.45 $ 43.00 $ 1.75 $187.00 $ 5.35 9.15 $ 41.00 $ 193.00 A dividend entry on the same line as a price indicates that the return between that time period and the previous period consisted of a capital gain (or loss) and the receipt of the dividend. Securities A, B and C can be referred to Securities 1, 2 and 3 respectively. For the purpose of this excercise only, please do not annualize your estimates. A. (3pts) Compute the rate of return for each company for each month (use simple return formula). B. (3pts) Compute the average rate of return for each company. C. (6pts) Compute the standard deviation of the rate of return for each company (use population variance formula. i.e. divide by N, not (N-1) ). D. (6pts) Compute the covariance and correlation coefficients between all possible pairs of securities (again, use population formula). E. (8pts) Compute the average return and standard deviation for the following portfolios: i. A+B ii. WA+C iii. 1 B + C iv. 1/3 A +1/3 B + 1/3 C F. (15 pts) Assuming short selling is not allowed: i. (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk. ii. (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2 iii. (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient. iv. (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1. 115pts Assuming short selling is allowed. F. Weights SD E[r] 2 2 2 Security pair i. A&B i. A&C i. B&C 2 1 Security pair ii. A&B iii. A&B graph goes somewhere here 2 1 graph goes somewhere here ii. A&C iii. A&C 2 1 ii. B&C iii. B&C graph goes somewhere here F. i. (15pts) Assuming short selling is not allowed: (2) For securities 1 and 2 find the composition, standard deviation, and expected return of that portfolio that has minimum risk. ii. (2) On the same graph plot the expected return and standard deviation for all possible combinations of securities 1 and 2 iii. (1) Assuming that investors prefer more to less and are risk avoiders, indicate those sections of the diagram in part ii) that are efficient. iv. (10) Repeat steps i), ii) and iii) for all other possible pairwise combinations of securities in Problem 1. G 1150te Assuming short selling is allowed
