1 a. I was given a set of data with 60 prices and then I calculated the return % in excel. The assignment is to calculate the average return and standard deviation of returns. I used the average formula for the average return, but I am not sure how exactly I need to calculate the standard deviation of returns. What is the formula, and do I input all of the data or just the average return to calculate?

b. the next step I need formulas for is to calculate the beta bi and the required rate of return using CAPM.

c. Part c is to estimate the correlations between the returns. Not sure how to do so. It wants a numerical answer.

I will attach the instructions at the beginning of the assignment for reference.

Home Insert Draw Page Layout fxx 2 .. Insert AutoSum Recently Financial Logical Function Used 698 x fx AutoSave OFF OFF HR Suu 2 3 4 15 7 8 Ticker 9 AGSPC 10 DIS 11 ADP 12 13 Inputs: 14 16 17 18 19 20 A 21 22 23 24 25 26 Risk-free rate, Fr Market risk premium, RPM B % of The Walt Disney Company in Portfolio % of Automatic Data Processing, Inc. in Portfolio Ready Start Here 1 Part 1. Assume that you recently graduated and landed a job as a financial planner with Cicero Services, an investment advisory company. Your first client recently inherited some assets and has asked you to evaluate them. Formulas 3.00% Text 6.00% C 50% Calculate average monthly return (annualized) and the standard deviation of returns for S&P 500 (^GSPC), The Walt 5 Disney Company (DIS), and Automatic Data Processing, Inc. (ADP). Name S&P 500 The Walt Disney Company Automatic Data Processing, Inc. 50% Observation Month-Year Note: *For 60 monthly returns (five years' worth of monthly returns) you will use 61 monthly prices. *Remember to annualize the monthly rate of return (simply multiply the calculated return by 12) *Assume that the risk-free rate is 3% and the market risk premium is 6%. Data O A Date & Lookup & Math & Time Reference Trig Review D The client owns a stock portfolio invested in The Walt Disney Company (DIS) and Automatic Data Processing, Inc. (ADP). You have observed monthly stock prices for S&P 500 (^GSPC), The Walt Disney Company (DIS), and Automatic Data Processing, Inc. (ADP) over the five-year period from December of 2015 to December of 2020. Grading E View Tell me + Mare Functions. F Define Name DIS ADP The Walt Disney Company atomatic Data Processing, In Price Returns % Price Returns Dec-2015 98.505562 73.551994 Jan-2016 90.400734 -98.7334% 72.582512 Feb-2016 90.117691 -3.7572% 73.980194 3 Mar-2016 93.693344 47.6131% 78.365372 4 Apr-2016 77.737923 1 2 97.419945 47.7293% IZGIGIN 05 2015 Project 1 v Create from Selection G Excel Project1_SU22 H ^GSPC S&P 500 Price 2,043.939941 -15.8171 % 1,940.239990 23.1077% 1,932.229980 71.1300% 2,059.739990 -9.6081% 2,065.300049 SABZ DIABEZ Trace Precedents Vfx Trace Dependents Error Watch Remove Arrows Formulas Checking Window Show Returns % I 50 points possible 3.2393% IN SAMPAL J K Calculate New Calculation Calculate Sheet Options L M N -60.8824% The values in D23, F23, and H23 are correct You can use them to check your formulas -4.9540% 79.1893% # Share Q Comments P + 125% T Home Insert Draw Page Layout fxx 2 .. Insert AutoSum Recently Financial Logical Function Used 698 x fx AutoSave OFF OFF HR Suu 2 3 4 15 7 8 Ticker 9 AGSPC 10 DIS 11 ADP 12 13 Inputs: 14 16 17 18 19 20 A 21 22 23 24 25 26 Risk-free rate, Fr Market risk premium, RPM B % of The Walt Disney Company in Portfolio % of Automatic Data Processing, Inc. in Portfolio Ready Start Here 1 Part 1. Assume that you recently graduated and landed a job as a financial planner with Cicero Services, an investment advisory company. Your first client recently inherited some assets and has asked you to evaluate them. Formulas 3.00% Text 6.00% C 50% Calculate average monthly return (annualized) and the standard deviation of returns for S&P 500 (^GSPC), The Walt 5 Disney Company (DIS), and Automatic Data Processing, Inc. (ADP). Name S&P 500 The Walt Disney Company Automatic Data Processing, Inc. 50% Observation Month-Year Note: *For 60 monthly returns (five years' worth of monthly returns) you will use 61 monthly prices. *Remember to annualize the monthly rate of return (simply multiply the calculated return by 12) *Assume that the risk-free rate is 3% and the market risk premium is 6%. Data O A Date & Lookup & Math & Time Reference Trig Review D The client owns a stock portfolio invested in The Walt Disney Company (DIS) and Automatic Data Processing, Inc. (ADP). You have observed monthly stock prices for S&P 500 (^GSPC), The Walt Disney Company (DIS), and Automatic Data Processing, Inc. (ADP) over the five-year period from December of 2015 to December of 2020. Grading E View Tell me + Mare Functions. F Define Name DIS ADP The Walt Disney Company atomatic Data Processing, In Price Returns % Price Returns Dec-2015 98.505562 73.551994 Jan-2016 90.400734 -98.7334% 72.582512 Feb-2016 90.117691 -3.7572% 73.980194 3 Mar-2016 93.693344 47.6131% 78.365372 4 Apr-2016 77.737923 1 2 97.419945 47.7293% IZGIGIN 05 2015 Project 1 v Create from Selection G Excel Project1_SU22 H ^GSPC S&P 500 Price 2,043.939941 -15.8171 % 1,940.239990 23.1077% 1,932.229980 71.1300% 2,059.739990 -9.6081% 2,065.300049 SABZ DIABEZ Trace Precedents Vfx Trace Dependents Error Watch Remove Arrows Formulas Checking Window Show Returns % I 50 points possible 3.2393% IN SAMPAL J K Calculate New Calculation Calculate Sheet Options L M N -60.8824% The values in D23, F23, and H23 are correct You can use them to check your formulas -4.9540% 79.1893% # Share Q Comments P + 125% T