Question

In: Finance

Risk and Return using EXCEL Use the following data to explore the return-risk relation and the...

Risk and Return using EXCEL

Use the following data to explore 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

2017 | $149.04 | $93.85 | 2,425.17

2016 | $95.89 | $59.60 | 2,102.94

2015 | $124.50 | $68.25 | 2,076.79

2014 | $94.03 | $57.05 | 1,960.96

2013 | $60.93 | $56.49 | 1,606.28

2012 | $55.78 | $37.12 | 1,325.66

Part 1: Risk and Beta

A) Calculate the return each year for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the following equation:

Value t - Value t-1

Return = -------------------------------------

Value t-1

In addition, use the Excel function to find the average for each corporation. (8 Points)

B) Calculate the standard deviation of returns for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the Excel function. (8 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. (8 Points)

AAPL JPM
Slope from Linear trendline Slope from linear trendline

D) For each stock, use the Excel function to calculate the correlation between the stock returns and market returns. Furthermore, copy the standard deviations (from part B) and calculate the beta for each stock. (8 Points)

Standard Deviation stock

Beta = ---------------------------------------------- ( Correlation between stock and market )

Standard Deviation market

Correlation Stock Standard Deviation (decimal) Market Standard Deviation (decimal) Beta
AAPL
JPM

Part 2: Required Return

E) Assume a market risk premium of 5.40% and a risk free-free rate of 1.31%. Calculate the expected return on the market. Also calculate the required return for Apple and JPMorgan Chase & Co according to the CAPM. (8 Points)

F) If you formed a portfolio that consisted of 50% Apple stock and 50% JPMorgan Chase & Co stock, what would be its beta and its required return? (8 Points)

Beta Portfolio Weight
AAPL
JPM
Porfolio Beta
Portfolio Required Return

G) 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.68, 0.98, and 1.43, respectively. Calculate the new portfolio's beta and required return if it consists of 25% of Apple, 15% of Stock A, 40% of Stock B, and 20% of Stock C. (8 Points)

Beta Portfolio Weight
Apple
Stock A
Stock B
Stock C
Portfolio Beta
Risk-free Rate Market Risk Premium Portfolio Beta Required Return on Portfolio

Solutions

Expert Solution

Part 1 - Risk and Beta

a)

Years Apple JPM S&P Returns - Apple Returns - JPM Market Returns (S&P)
2017 149.04 93.85 2425.17 0.554280947 0.57466443 0.153228337
2016 95.89 59.6 2102.94 -0.229799197 -0.126739927 0.012591548
2015 124.5 68.25 2076.79 0.324045517 0.196319018 0.089057977
2014 94.03 57.05 1906.96 0.543246348 0.009913259 0.187190278
2013 60.93 56.49 1606.28 0.092326999 0.521821121 0.211683237
2012 55.78 37.12 1325.66
Average Return 0.256820123 0.23519558 0.130750275

b) Standard Deviation

Apple JPM S&P
0.331247 0.308486 0.080530451

d) use Correl function to calculate correlation between stock and market (values required for array 1 and array 2 which would be market returns)

Correlation Stock Standard Deviation (decimal) Market Standard Deviation (decimal) Beta
AAPL 0.602003 0.331247 0.080530451 2.47622713
JPM 0.62896 0.308486 0.080530451 2.40934146

Part 2 - Required Return

e)

CAPM - Risk free rate + Beta *(Market return - Risk free return)

Apple - 1.31 + 2.48*(5.40) - 14.70

JPM - 1.31 + 2.41*(5.40) - 14.324

Expected return on market - 6.71 (1.31 + 1*5.40) beta will always be one for the market.

f)

Portfolio Beta - 2.445 (weighted average of the beta of individual stocks) - 0.50*2.48+0.5*2.41

Portfolio Expected Return - 14.512 (weighted average of the expected returns of the stocks calculated in previous step) - 0.5*14.7+0.5*14.324

g) Portfolio beta - 1.4 (0.25*2.48+0.15*0.68+0.40*0.98+0.25*1.43)

assuming risk free rate - 1.31 equity risk premium - 5.4

required portfolio returns - 1.31 + 1.4* 5.4 = 8.87


Related Solutions

Risk and Return Use the following data to explore the risk-return relation and the concept of...
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 Calculate the return each year for Apple, Kroger, and the Market using...
Risk and Return Use the following data to explore the risk-return relation and the concept of...
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 2: Required Return Market risk premium: RPM = 5.60% Risk-free rate: rRF = 0.70% Calculate the...
Use the following data to explore the risk-return relation and the concept of beta for Apple...
Use the following data to explore the risk-return relation and the concept of beta for Apple stock, Alphabet (Google) stock, and the S&P 500 market index: Year Apple Stock Price Alphabet Stock Price S&P 500 Market Index 2017 $174.09 $1,072.01 2,601.42 2016 $115.82 $807.80 2,238.83 2015 $105.26              $765.84 2,043.94 2014 $113.99 $521.51 2,058.90 2013 $80.01 $559.76 1,848.36 2012 $72.80 $358.17 1,426.19 Market risk premium: RPM = 4.01% Risk-free rate: rRF = 1.30% Calculate the portfolio beta of the four-stock portfolio...
Use the following data to explore the risk-return relation and the concept of beta for Apple...
Use the following data to explore the risk-return relation and the concept of beta for Apple stock, Alphabet (Google) stock, and the S&P 500 market index: Year Apple Stock Price Alphabet Stock Price S&P 500 Market Index 2017 $174.09 $1,072.01 2,601.42 2016 $115.82 $807.80 2,238.83 2015 $105.26              $765.84 2,043.94 2014 $113.99 $521.51 2,058.90 2013 $80.01 $559.76 1,848.36 2012 $72.80 $358.17 1,426.19 E) Calculate the expected return on the market according to: Expected Return on Market = Risk-Free Rate + Market...
Use the data below to solve the following problem using excel: 1 a) Import the data...
Use the data below to solve the following problem using excel: 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in each cell to determine the class for each vehicle. i. Class 1 if the vehicle horsepower is less than 80 HP. ii. Class 2 if the vehicle horsepower is between 81 and...
(PLEASE READ ) :) Use the data below to solve the following problem using excel: (...
(PLEASE READ ) :) Use the data below to solve the following problem using excel: ( I would like to know how do you input the formula for each category, so please explain the process) I will RATE and comment your answer accordingly 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression line. X-Bar 2 4 5 6 Y-Bar 7 11 13 20
Using the data from this module’s Connect Excel Simulation - "Calculation of Return of Investment and...
Using the data from this module’s Connect Excel Simulation - "Calculation of Return of Investment and Residual Income for Adam Corporation’s Northern Division", compare and contrast how the calculations may have impacted the division manager’s decision regarding accepting or rejecting a new product line. Include discussion of the limits of using ROI vs Residual Income for evaluating performance of the division. Do you think the division manager’s decision to invest would change if his/her bonus was based on company ROI...
THIS PROBLEM NEEDS TO BE SOLVED ONLY USING EXCEL SOFTWARE! 1. Use the following data set...
THIS PROBLEM NEEDS TO BE SOLVED ONLY USING EXCEL SOFTWARE! 1. Use the following data set to answer the following: ew dbh e 23.5 e 43.5 e 6.6 e 11.5 e 17.2 e 38.7 e 2.3 e 31.5 e 10.5 e 23.7 e 13.8 e 5.2 e 31.5 e 22.1 e 6.7 e 2.6 e 6.3 e 51.1 e 5.4 e 9 e 43 e 8.7 e 22.8 e 2.9 e 22.3 e 43.8 e 48.1 e 46.5 e 39.8...
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for...
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for the problem in the first worksheet named LightbulbLife of the data table down below It gives the data on the lifetime in hours of a sample of 50 lightbulbs. The company manufacturing these bulbs wants to know whether it can claim that its lightbulbs typically last more than 1000 burning hours. So it did a study. Identify the null and the alternate hypotheses for...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT