Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Risk and Return Use the following data to explore the risk-return relation and the concept of beta for Apple stock, Kroger stock, and the S&P

Risk and Return

Use the following data to explore the risk-return relation and the concept of beta for Apple stock, Kroger stock, and the S&P 500 market index:

Year

Apple Stock Price

Kroger Stock Price

S&P 500 Market Index

2020

$252.92

$30.71

3,234.85

2019

$156.23

$27.66

2,531.94

2018

$169.23

$27.32

2,753.15

2017

$125.17

$29.52

2,276.98

2016

$108.41

$31.80

2,043.94

2015

$112.98

$33.37

2,058.20

Part 1: Risk and Beta

  1. Calculate the return each year for Apple, Kroger, and the Market using the equation:

Return = (Value this year Value last year) / Value last year. In addition, use the Excel function Average to find the average of the returns.

image text in transcribed

  1. Calculate the standard deviation of rates of return for Apple, Kroger, and the Market using the Stdev Excel function.

image text in transcribed

  1. HOW DO YOU DO THIS IN EXCEL??? Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Apple and Kroger stock in one plot. Add a linear trend line to the scatter plot for each stock and include the equation on the chart (Select data point and right click then select "add trendline" and choose a "linear" trend and select "display equation on chart"). Label the y-axis, x-axis, legend, and chart title.

  1. For each stock, use the Excel function Correl to calculate the correlation between the stock returns and market returns. Furthermore, copy the standard deviations from above (by cell reference) and calculate the beta according to the equation: Beta = (Standard deviation of stock / Standard deviation of market) (Correlation between stock and market).

image text in transcribed

  1. Calculate the expected return on the market according to: Expected Return on Market = Risk-Free Rate + Market Risk Premium. Also calculate the required return for Apple and Kroger according to: Required Return = Risk-Free Rate + (Beta)(Market Risk Premium).
  2. image text in transcribed

  1. If you formed a portfolio that consisted of 60% Apple stock and 40% Kroger stock, calculate the beta.
  2. image text in transcribed

  1. Calculate the portfolio beta of the four-stock portfolio and the required return on the portfolio.
  2. image text in transcribed
Year Kroger Returns S&P 500 Market Index Returns 18 A) Rates of return 19 20 21 22 23 24 25 26 27 28 29 Apple Returns 2020 2019 2018 2017 2016 Average Returns 31 B) Standard Deviation of Returns 32 33 Apple Standard Deviation of Returns 34 35 Kroger Standard Deviation of Returns S&P 500 Market Standard Deviation of Returns Correlation Stock Standard Deviation (in decimal form) Market Standard Deviation (in decimal form) Beta 57 D) Beta 58 59 60 61 62 Apple Kroger Expected return on the market:rm = 72 E) Required Return 73 74 75 76 77 78 Apple Required Return = Kroger Required Return = 80 F) Portfolio Beta 81 82 Beta Portfolio Weight 83 Apple Kroger 84 85 Portfolio Beta 86 87 89 G) Required Portfolio Return 90 91 Beta Portfolio Weight 92 93 Apple Kroger Stock D Stock C 25% 15% 40% 20% 94 1.52 1.42 95 96 97 Portfolio Beta = 98 Risk-free rate 99 100 101 102 Market Risk Premium Portfolio Beta Required Return on Portfolio Year Kroger Returns S&P 500 Market Index Returns 18 A) Rates of return 19 20 21 22 23 24 25 26 27 28 29 Apple Returns 2020 2019 2018 2017 2016 Average Returns 31 B) Standard Deviation of Returns 32 33 Apple Standard Deviation of Returns 34 35 Kroger Standard Deviation of Returns S&P 500 Market Standard Deviation of Returns Correlation Stock Standard Deviation (in decimal form) Market Standard Deviation (in decimal form) Beta 57 D) Beta 58 59 60 61 62 Apple Kroger Expected return on the market:rm = 72 E) Required Return 73 74 75 76 77 78 Apple Required Return = Kroger Required Return = 80 F) Portfolio Beta 81 82 Beta Portfolio Weight 83 Apple Kroger 84 85 Portfolio Beta 86 87 89 G) Required Portfolio Return 90 91 Beta Portfolio Weight 92 93 Apple Kroger Stock D Stock C 25% 15% 40% 20% 94 1.52 1.42 95 96 97 Portfolio Beta = 98 Risk-free rate 99 100 101 102 Market Risk Premium Portfolio Beta Required Return on Portfolio

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Sustainable Finance And Impact Investing

Authors: Alan S. Gutterman

1st Edition

1637423764, 978-1637423769

More Books

Students also viewed these Finance questions