Risk and Return

The goal of this project is to explore the topic of risk and return. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit and reference cells, employ formulas, and use Excel functions where necessary (i.e., do not simply type in answers). In addition, type up a report in Word (2 pages) with a separate section for the introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (conclusions, summary). Make sure to follow all directions or points will be deducted. Each question (A, B, C, D, E, F, G) in the Excel file is worth 13 points (total of 91 points) and the report is worth 9 points.
Risk and Return
Use the following data to analyze the return-risk relation and the concept of beta for Apple stock, JPMorgan Chase & Co stock, and the S&P 500 market index:

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
Calculate the return each year (2017-2021) for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the following equation:
“Retur” “n” _t=(“Valu” “e” _t-“Valu” “e” _”t-1″ )/(“Valu” “e” _”t-1″ )
In addition, use the Excel average function to find the mean returns. (13 Points)

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)

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)

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_i=(σ_i/σ_M )(ρ_”iM” )

Part 2: Required Return
Assume a market risk premium of 5.60% and a risk free-free rate of 1.37%. Calculate the expected return on the market (use the formula for RPM). Also calculate the required return (ri) for Apple and JPMorgan Chase & Co according to the CAPM. (13 Points)
For a portfolio that consists of 30% Apple stock and 70% JPMorgan Chase & Co stock, calculate the beta and required return? (13 Points)

Suppose an investor wants to include Apple stock in their portfolio. Stocks A, B, and C are currently in the portfolio, and their betas are 0.62, 0.95, and 1.43, respectively. Calculate the new portfolio’s beta and required return if it consists of 20% of Apple, 10% of Stock A, 40% of Stock B, and 30% of Stock C. (13 Points)

Excel Tips
For the AVERAGE Excel function see: https://support.office.com/en-us/article/AVERAGE-function-047bac88-d466-426c-a32b-8f33eb960cf6
For the STDEV Excel function see: https://support.office.com/en-US/article/STDEV-function-51FECAAA-231E-4BBB-9230-33650A72C9B0
For the Trendline information see: https://support.office.com/en-US/article/add-change-or-remove-a-trendline-in-a-chart-fa59f86c-5852-4b68-a6d4-901a745842ad
For the CORREL Excel function see: https://support.office.com/en-US/article/CORREL-function-995DCEF7-0C0A-4BED-A3FB-239D7B68CA92

Essay Mill