CoursHeroTranscribedText: FINC 3304 - Business Finance Project 4 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. Risk and Return 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 2016 2015 2014 2013 2012 2011 Apple Stock Price $115.82 $105.26 $113.99 $80.01 $72.80 $57.86 Alphabet Stock Price $807.80 $765.84 $521.51 $559.76 $358.17 $323.37 S&P 500 Market Index 2238.83 2043.94 2058.90 1848.36 1426.19 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 (8 Points) B) Calculate the standard deviation of rates of return for Apple, Alphabet, and the Market 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 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 (8 Points) 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) (8 Points) Part 2: Required Return E) Calculate the expected return on the market according to Expected Return on Market = RiskFree Rate + Market Risk Premium. Also calculate the required return for Apple and Alphabet according to Required Return = Risk-Free Rate + (Beta)(Market Risk Premium) (8 Points) F) If you formed a portfolio that consisted of 50% Apple stock and 50% Alphabet stock, what would be its beta? (8 Points) G) Calculate the portfolio beta of the four stock portfolio and the required return on the portfolio (8 Points) FINC 3304 - Project 4 - Risk and Return Last Name First Name Part 1: Risk and Beta Year Apple Stock Price 2016 2015 2014 2013 2012 2011 Alphabet Stock Price $115.82 $105.26 $113.99 $80.01 $72.80 $57.86 S&P 500 Market Index $807.80 $765.84 $521.51 $559.76 $358.17 $323.37 2,238.83 2,043.94 2,058.90 1,848.36 1,426.19 1,257.60 A) Rates of return Year Apple Stock Price Alphabet Stock Price Market 2016 2015 2014 2013 2012 Hint: Return = (Value This Year - Value Last Year) / Value Last Year Average (See https://support.office.com/en-us/article/average-function-7f473887-4818-421 Hint: Use the excel function "=Average(Range of values)" B) Standard Deviation of Returns Apple Stock Price Alphabet Stock Price Market (https://support.office.com/en-US/article/STDEV-function-a0179959-f6e4-47ec-8818-a0b43026223f) Hint: Use the excel function "=Stdev(Range of values)" C) Graph Stock Returns and Market Returns Hint: Right click on graph and choose "select data" and then "add" Hint: Select data point and right click then select "add trendline" and choose a "linear" trend and select "display Stock Return vs. M ar ket Return s D) Beta Correlation Stock Standard Deviation (in decimal form) Market Standard Deviation (in decimal form) Beta Apple Alphabet (https://support.office.com/en-US/article/CORREL-function-995DCE Hint: Use the Excel function "=correl(range of numbers, range of nu Hint: Beta = (Standard deviation of stock / Standard deviation of ma Part 2: Required Return Market risk premium: RPM = Risk-free rate: rRF = 3.92% 2.29% E) Required Return Expected return on the market: rm = Hint: Expected Return on Market = Risk-Free Rate + Market Risk Premium Apple Required Return = Alphabet Required Return = Hint: Required Return = Risk-Free Rate + (Beta)(Market Risk Premium) F) Portfolio Beta Beta Portfolio Weight Apple Alphabet Portfolio Beta Hint: Portfolio Beta = (W1)(B1) + (W2)(B2)