Calculate the standard deviation of returns

Assignment Help Financial Management

Reference no: EM131994036

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.

B) Calculate the standard deviation of returns for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the Excel function.

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.

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.

Standard Deviation stock

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

Standard Deviation market

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.

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?

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.

Reference no: EM131994036

Questions Cloud

Security percent of portfolio return : Security Percent of portfolio Return Stock A 57% 0.0% Stock B 16% 16.4% Stock C Please calculate it 7.0% Calculate the expected return of portfolio.

What is your estimate of current stock price : What is your estimate of the current stock price (P0)? What is the target stock price in year 4 (P4)?

What is the capital gain yield and dividend yield : What is the required return (r)? What is the dividend yield? What is the capital gain yield?

Consolidated financial statements : Consolidated financial statements are:

Calculate the standard deviation of returns : Calculate the standard deviation of returns for Apple, JPMorgan Chase & Co, and the S&P 500 market index using the Excel function.

How is the property classified for macrs : How is the property classified for MACRS? What is the life of the asset for MACRS? 27.5 years. Terry's cost recovery deduction for 2017 is ?

Severity of the great depression on the federal reserve : Many researchers have blamed the severity of the Great Depression on the Federal Reserve and its failure to react to economic conditions in 1929 and 1930.

What is the break-even point in bags : What is the break-even point in bags? Calculate the profit or loss (EBIT) on 16,000 bags and on 34,000 bags.

Non-operating activities on the income statement : Non-operating activities on the income statement are. Normally, the first note to the financial statements is.

User Account

All Pages