Problem Four

You work for a small investment management firm. You have beenprovided with the following historical information for three stocksand the market index. The information is shown in the tablebelow.

	AAA Inc.		BBB Inc.		CCC Inc.
	Stock Price	Dividend	Stock Price	Dividend	Stock Price	Dividend	Market Index
2012	$35	$1.20	$110	$0.45	$100	$1.50	10,840
2013	$40	$1.40	$120	$0.50	$85	$1.50	11,320
2014	$45	$1.40	$98	$0.55	$95	$1.60	12,220
2015	$50	$1.70	$115	$0.65	$110	$1.65	13,325
2016	$42	$1.85	$140	$0.75	$105	$1.65	12,500
2017	$52	$1.85	$150	$0.90	$125	$1.75	13,820
2018	$70	$1.90	$120	$1.00	$140	$1.90	14,400

Using the data provided you are to calculate the following foreach of the stocks and the market index.

1. What is the annual return for the past six years?
2. What is the average annual return for the past six years?
3. What is the geometric average annual return (effective annualrate of return) for the past six years?
4. What is the population standard deviation and the samplestandard deviation?
5. What is the sample covariance and correlation coefficientbetween Stocks AAA and BBB? Between Stocks AAA and CCC? BetweenStocks BBB and CCC?
6. What is the coefficient of variation for each of the stocks andthe market index?
7. What is the beta of each of the stocks? You can use Excel’s“slope” function to estimate this.
8. Assuming that the beta of stock AAA is 0.80, the beta of stockBBB is 1.2 and the beta of stock CCC is 1.6. What is the beta of aportfolio where the weight in stock AAA is 20% the weight in stockBBB is 50% and the weight in stock CCC is 30%.
1. Assuming the risk free rate is 3% and the expectedreturn on the market is 7% what is the expected return on theportfolio in part (h) above?

Please answer question using excel functions with in cellviewing.