Year Apple Stock Price JPMorgan Chase & Co Stock Price S&P 500 Market Index Value 2021...

 

Transcribed Image Text:

Year Apple Stock Price JPMorgan Chase & Co Stock Price S&P 500 Market Index Value 2021 $129.41 $125.87 3,792.00 2020 $75.09 $141.09 3,259.00 2019 $39.48 $99.31 2,511.00 2018 $43.07 $107.95 2,692.90 2017 $29.04 $87.23 2,252.60 2016 $26.34 $63.62 2,009.10 Part 1: Risk and Beta A) Calculate the return each year (2017-2021) for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the following equation: Return = Value+-Value+-1 Value+-1 In addition, use the Excel average function to find the mean returns. (13 Points) B) Calculate the sample standard deviation of returns for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the Excel sample standard deviation function. (13 Points) C) Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Apple and JPMorgan Chase & Co stock in one plot. Add a linear trendline to the scatter plot for each stock and include the equation on the chart. Identify the slope for each stock from the trendline equation. Label the y-axis, x-axis, legend, and chart title. (13 Points) D) For each stock, use the Excel correl function to calculate the correlation between the stock returns and market returns. Furthermore, reference the standard deviations (from part B) and calculate the beta for each stock according to the formula. (13 Points) b = (01) (PIM) Year Apple Stock Price JPMorgan Chase & Co Stock Price S&P 500 Market Index Value 2021 $129.41 $125.87 3,792.00 2020 $75.09 $141.09 3,259.00 2019 $39.48 $99.31 2,511.00 2018 $43.07 $107.95 2,692.90 2017 $29.04 $87.23 2,252.60 2016 $26.34 $63.62 2,009.10 Part 1: Risk and Beta A) Calculate the return each year (2017-2021) for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the following equation: Return = Value+-Value+-1 Value+-1 In addition, use the Excel average function to find the mean returns. (13 Points) B) Calculate the sample standard deviation of returns for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the Excel sample standard deviation function. (13 Points) C) Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Apple and JPMorgan Chase & Co stock in one plot. Add a linear trendline to the scatter plot for each stock and include the equation on the chart. Identify the slope for each stock from the trendline equation. Label the y-axis, x-axis, legend, and chart title. (13 Points) D) For each stock, use the Excel correl function to calculate the correlation between the stock returns and market returns. Furthermore, reference the standard deviations (from part B) and calculate the beta for each stock according to the formula. (13 Points) b = (01) (PIM)