[meteor_slideshow slideshow=”arp1″]
This is your first week at a prestigious brokerage firm as an equity analyst. Your boss
asked you to estimate the expected return of the following stocks – Exxon Mobil
Corporation (XOM) and The Goldman Sachs Group, Inc. (GS).
1) Download the adjusted stock prices for XOM and GS for the last 5 years (1/2007-1/2012)
from Yahoo-finance (https://finance.yahoo.com/).
a. Enter the ticker symbol “XOM” and then click on “Historical Prices”.
b. Set the date range (1/1/2007, 31/1/2012). Select “monthly” and then click on “get
prices”. To download the data to a spreadsheet click on “Download to
Spreadsheet” link below the table.
c. Delete the columns “Open”, “High”, “Low”, “Close” and “Volume”. You will
need “Date” and the “Adj Close” columns. The latter one is the close price
adjusted for dividends and splits.
d. Repeat the steps a and b for the Goldman Sachs Group, Inc. (GS) and S&P 500
(^GSPC).
e. Copy and paste these four columns (“date” and “adj close” for GS and S&P 500)
next to “adj close” for XOM.
f. After making sure that dates match for XOM, GS and S&P 500 delete the two of
the “date” columns. After this step you should have “Date” as column A, “Adj
Close” for XOM, GS, and S&P 500 as columns B, C, and D, respectively.
g. Select all observations and then click on “Sort & Filter – Custom sort: Sort by :
Date, Sort on: Values, Order: Oldest to newest”.
h. Compute the monthly returns for XOM, GS and S&P 500. (eg. For 2007 January
return of XOM you should use the following equation: =B3/B2-1 (end of month
adj close/ beginning of month adj close -1).
i. Drag this formula to the rest of the observations till 12/1/2011 (row 61).
j. Repeat the same procedure (h and i) for GS and S&P 500.
k. Copy and paste risk free rates (available on Blackboard). Make sure that dates
match.
l. Compute risk premiums (ret-rf) for each month for XOM, GS, S& 500.
m. To estimate beta for XOM, use slope function (=slope( , )). Select risk premium
for XOM as y (60 observations), and risk premium for S&P 500 as x (60
observations).
n. Repeat m for GS.
o. Print out the spreadsheet.
2) What is the beta of XOM? What is the beta of GS?
3) Using the beta estimates and a market risk premium of 6% and a risk free rate of 1%
compute expected returns for GS and XOM.
4) Which one is aggressive stock? Which one is defensive stock? Why?
PLEASE answer the question too in the same excel sheet
Place your order of custom research paper With us NOW. The assignment will be written from scratch by our qualified and experienced writers.
[meteor_slideshow slideshow=”arp2″]
A-Research-Paper.com is committed to deliver a custom paper/essay which is 100% original and deliver it within the deadline. Place your custom order with us and experience the different; You are guaranteed; value for your money and a premium paper which meets your expectations, 24/7 customer support and communication with your writer. Order Now
Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.
[order_calculator]