Question

In: Finance

Obtain monthly adjusted close prices of Microsoft (symbol: MSFT) and Exxon Mobil Corporation (symbol: XOM) during...

Obtain monthly adjusted close prices of Microsoft (symbol: MSFT) and Exxon Mobil Corporation (symbol: XOM) during December 1989 – December 2019 from Yahoo Finance. Using Excel, compute monthly returns of each company during January 1990 – December 2019. Also, compute and report the arithmetic average and the standard deviation of each company’s returns. Compute and report the correlation coefficient between these two stock returns. Next, form a portfolio that combines Microsoft and Exxon Mobil Corporation with equal weight. Compute monthly returns on this newly formed portfolio. Also, compute and report the arithmetic average and the standard deviation of this portfolio’s returns. Is the portfolio standard deviation smaller than the weighted average of individual standard deviations? Also, compute and report the standard deviation of this portfolio’s returns using the equation that we learned in class. Were you able to obtain the same answer?

Guidelines:

1. Use the STDEV.P( : ) command to compute standard deviation.

2. Use the CORREL( : , : ) command to compute correlation coefficient.

Solutions

Expert Solution

1. Monthly returns :

Monthly returns are calculated as= Ending price -begin price / beign price

Formula =(B5-B4)/B4

=(C5-C4)/C4

2. Average return:

the average of the total monthly returns for the stock

=AVERAGE(D5:D364)

=AVERAGE(E5:E364)

3. Standard deviation of the stock is obtained for the following months

=STDEV.P(D5:D364)

=STDEV.P(E5:E364)

4. Correlation between the stocks is obtained by entering both the data sets

=CORREL(D5:D364,E5:E364)

5. Portfolio is constructed using both the stocks in equal weights

=(D5*0.5)+(E5*0.5)

6. Portfolio arithmetic avg return

=AVERAGE(F5:F364)

Std dev portfolio

=STDEV.P(F5:F364)

The final construction table is as follows

.

The Std dev and correlation is calculated as follows

Data table :

Thanks


Related Solutions

Obtain monthly close prices of Microsoft (symbol: MSFT) adjusted for stock splits and dividends during December...
Obtain monthly close prices of Microsoft (symbol: MSFT) adjusted for stock splits and dividends during December 1989 – December 2019 from Yahoo Finance (finance.yahoo.com). Using Excel, compute monthly returns during January 1990 – December 2019. From these returns, compute arithmetic and geometric return averages over the entire period. Report your answers and confirm that arithmetic average is greater than geometric average. Guideline: 1. Yahoo Finance allows you to download the data in a spreadsheet. 2. At Yahoo Finance, go to...
Obtain monthly adjusted close prices of S&P500 Index (symbol: ^GSPC) and Microsoft (symbol: MSFT) during December...
Obtain monthly adjusted close prices of S&P500 Index (symbol: ^GSPC) and Microsoft (symbol: MSFT) during December 2006 – December 2019 from Yahoo Finance. Then, using Excel, compute monthly returns during January 2007 – December 2019. From these returns, compute and report beta of Microsoft using the formula we learned in class. Suppose today’s Treasury bill rate is 2% per annum. Historically, market risk premium is estimated to be 8% per annum. What is today’s expected return per annum of Microsoft,...
You need to perform a valuation of XOM (Exxon-Mobil) in an environment where oil prices have...
You need to perform a valuation of XOM (Exxon-Mobil) in an environment where oil prices have fallen to less than zero. Which valuation method are you most likely to utilize? A Intrinsic Valuation B Relative Valuation C Contingent Claim Valuation D Replacement Cost Valuation
For company Exxon Mobil stock symbol XOM Liquidity (current ratio, quick ratio, and net working capital-to-sales...
For company Exxon Mobil stock symbol XOM Liquidity (current ratio, quick ratio, and net working capital-to-sales ratio) Operating performance ratio (Days of Sales in Inventory, Days of Sales in Receivables), turnovers Profitability ratios (Gross Profit Margin, Operating Profit Margin, Net Profit Margin) Return on Investment ratios: (Basic Earning Power ratio, ROA, ROE) You can find financial ratios for the company for the last 3-5 years in the Internet (www.morningstar.com) a) Present the ratios as the table(s) in your project. Create...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is traded at $5.60. The put option's delta is -0.65 and the call option's delta is 0.7. A) On October 3rd, XOM stock price changed to $71.15 on Oct 3rd, what will be the values of the put and call options? B) Consider a...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is traded at $5.60. The put option's delta is -0.65 and the call option's delta is 0.7. A) On October 3rd, XOM stock price changed to $71.15 on Oct 3rd, what will be the values of the put and call options? B) Consider a...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is traded at $5.60. The put option's delta is -0.65 and the call option's delta is 0.7. A) On October 3rd, XOM stock price changed to $71.15 on Oct 3rd, what will be the values of the put and call options? B) Consider a...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM...
On Sept 30th, 2011, Exxon Mobil (XOM) stock was traded at $72.63 while the December XOM put option with $75 exercise price is traded at $5.00 and the December XOM call option with $70 exercise price is traded at $5.60. The put option's delta is -0.65 and the call option's delta is 0.7. A) On October 3rd, XOM stock price changed to $71.15 on Oct 3rd, what will be the values of the put and call options? B) Consider a...
The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and...
The following table contains closing monthly stock prices for Oracle Corporation (ORCL), Microsoft Corporation (MSFT), and NVidia (NVDA) for the first half of 2017. Ticker 6/30/2017 5/31/2017 4/30/2017 3/31/2017 2/28/2017 1/31/2017 ORCL 50.14 45.39 44.96 44.61 42.59 40.11 MSFT 68.93 69.84 68.46 65.86 63.98 64.65 NVDA 144.56 144.35 104.3 108.93 101.48 109.18 Create a formula to calculate the monthly rate of return during the first semester of 2017 and for each company. Format the results as percentages with two decimal...
Exxon Mobil ( XOM) is one of the half- dozen major oil companies in the world....
Exxon Mobil ( XOM) is one of the half- dozen major oil companies in the world. The firm has four primary operating divisions ( upstream, downstream, chemical, and global services) as well as a number of operating companies that it has acquired over the years. A recent major acquisition was XTO Energy, which was acquired in 2009 for $ 41 billion. The XTO acquisition gave Exxon Mobil a significant presence in the development of domestic unconventional natural gas resources, including...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT