Question

Here is the answer to a question in my assignment. I was wondering how he got the 82.5144/103.143, not sure how he got those numbers. Can anyone explain it in an excel format?

Question 2 (3 Marks) Anna is a Vice President at the J Corporation. The company is considering investing in a new factory and Anna must decide whether it is a feasible project. In order to assess the viability of the project, Anna must first calculate the rate of return that equity holders expect from the company stock. The annual returns for J Corp. and for a market index are given below. Currently, the risk-free rate of return is 1.4% and the market risk-premium is 4.2% . 0.92 3 76 Enter your Final Answer Here a) What is the beta of J Corp.'s stock? (1 Mark)(Round your answer to two decimal places) b) Using the CAPM model, what is the expected rate of return on J Corp. stock for the coming year? (2 Mark)(Round your answer to one one-hundreth of a percent) J Corp. Market Return Return Year (%) (%) -3.76 -4.90 7.07 8.64 10.11 12.44 10.11 12.44 -10.72 -13.60 12.83 15.84 13.47 16.64 10.61 13.06 6.48 7.90 7.87 9.64 -4.25 -5.51 -4.32 -5.60 mo The beta of the stock is equal to the slope of a line-of-best-fit between the market and stock returns beta = cov(M,J) /var(M) beta = 82.5144 103.143 beta = 0.8 b) To calculate the expected return using the CAPM model, we use the formula r=[++ B (risk premium) 1.4% 4.76% 0.8 x l 4.2% )

Answer 1

For 82.5144,
Type '=COVARIANCE.S' in an excel cell and select J Corp returns from year 1 to year 10 in array 1 then type ',' and select Market returns from year 1 to year 10 and close the bracket and press ENTER.

For 103.143,
Type '=STDEV.S' in an excel cell and select Market Returns from year 1 to year 10 and then close the bracket and press ENTER.
Square the answer that you get from the above step which is equal to variance i.e 103.143