Calculate the portfolio beta of the four stock portfolio

Reference no: EM131742661 , Length: 1

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. In addition, type up a report in Word with an introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (conclusions, summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Each question (A, B, C, D, E, F, and G) is worth 8 points (total of 56 points), the report 10 points, and the Excel file 9 points.

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
2016	$115.82	$807.80	2238.83
2015	$105.26	$765.84	2043.94
2014	$113.99	$521.51	2058.90
2013	$80.01	$559.76	1848.36
2012	$72.80	$358.17	1426.19
2011	$57.86	$323.37	1257.60

Part 1: Risk and Beta

A) Calculate the return each year for Apple, Alphabet, and the Market using the equation Return
= (Value this year - Value last year) / Value last year. In addition, use the Excel function to find the average of the returns

B) Calculate the standard deviation of rates of return for Apple, Alphabet, and the Market using the Excel function

C) Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Apple and Alphabet stock in one plot. Add a linear trend line to the scatter plot for each stock and include the equation on the chart. 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, label the standard deviations and calculate the beta according to the equation, Beta = (Standard deviation of stock / Standard deviation of market)
*(Correlation between stock and market)

Part 2: Required Return

E) 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 Alphabet according to Required Return = Risk-Free Rate + (Beta)(Market Risk Premium)

F) If you formed a portfolio that consisted of 50% Apple stock and 50% Alphabet stock, what would be its beta?

G) Calculate the portfolio beta of the four stock portfolio and the required return on the portfolio

Attachment:- Project - Risk and Return.xlsx

Reference no: EM131742661

Questions Cloud

What if chante tell the occurrence to the company : What if Chante tell this occurrence to the company. Take Chante out to thelunch to get to know her and introduce the company.

Find the mean number of broken eggs : Eggs. A grocery supplier believes that in a dozen eggs, the mean number of broken ones is 0.6 with a standard deviation of 0.5 eggs.

Determine the location using demographic data : Explain your selection citing information and giving reasons. Justify your decision to use secondary data rather than conducting a primary research project.

Sean has been diagnosed with moderate schizophrenia : Sean has been diagnosed with moderate Schizophrenia. In an effort to improve his ability to socialize appropriately,

Calculate the portfolio beta of the four stock portfolio : FINC 3304 - Business Finance - Calculate the return each year for Apple, Alphabet, and the Market using the equation Return and Calculate the expected return

Draw a hypothetical organizational chart for the previous : Draw a hypothetical organizational chart for the previous and new structures. Describe the differences and explain the impact this will have on the company

Discuss a final contemplation of horror : the company of obsessed or deranged characters, and end up with a final contemplation of horror

How many bad seeds do you expect to get : Garden. A company selling vegetable seeds in packets of 20 estimates that the mean number of seeds that will actually grow is 18, with a standard deviation.

Kara situation and why should you take approach : She hardly ever speaks and has lots of trust issues. How can you help Kara's situation and why should you take this approach?

User Account

All Pages