До 19.03 выполнить задание по корпоративным финансам

выполнить задание, описанное ниже, текст задания могу перевести на русский

дедлайн 19.03 19:00

все данныы об акциях нужно брать с https://finance.yahoo.com/ 

текст задания:

The homework should be submitted as single MS Excel file. Answer to each question should be easily identifiable. Answers should be found with MS Excel formulas. Answers as a plain number will not be counted. Dividends can be ignored. 

1. Download to a spreadsheet the last three years (2019, 2018 & 2017) of daily stock prices for five stocks assigned by your teacher and for S&P 500.

a. Calculate the daily returns of each stock.

b. Calculate the arithmetic average daily return, HPR and CAGR for each stock for 2017-2019.

c. Estimate the expected annual return for each stock using the expected daily return in 2019. Compare it with the real annual return in 2019.

d. Calculate the risk of the daily returns of each stock in 2017-2019. For risk use variance and standard deviation.

e. Calculate the beta-coefficients for each stock. Use S&P 500 as market portfolio.

f. Calculate the correlation matrix of the daily returns of your 5 stocks during 2017-2019.

2. Let us assume that in the beginning of 2017 you have $100 000 to invest. You consider two investment strategies. The first one is choosing 2 stocks out of the 5 assigned by teacher with the lowest correlation coefficient and invest half of your assets in each stock.

a. Which two stocks would you buy? What is the correlation coefficient for their returns?

b. How many stocks of each company would you buy if you do not spend more than the $50 000 on each?

c. Calculate the daily returns, HPR and CAGR of the portfolio for 2017-2019.

d. Calculate the risk of the portfolio in 2017-2019.

3. The second strategy is to invest $20 000 in each of your 5 stocks.

a. How many stocks of each company сould you buy if you do not spend more than the $20 000 on each?

b. Calculate the daily returns, HPR and CAGR of the portfolio for 2017-2019.

c. Calculate the risk of the portfolio in 2017-2019.

4. Compare those two strategies. Which one had a higher risk? Which one had a higher return?