# This assignment will require you to analyze

Mini-Case 1

This assignment will require you to analyze time series of monthly returns. Start by retrieving MONTHLY data for the period of 1 August, 2013 – 1 August, 2016 from Yahoo website for

− S&P 500 Index (ticker: ^GSPC)

− Exxon Mobil Corporation (ticker: XOM)

− Facebook, Inc. (ticker: FB)

Instructions for downloading the data from Yahoo!:

To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click on “Historical Data”. Enter “Time Period” as given above. For “Frequency”, make sure Monthly is selected and then click on “Apply”. Click on “Download Data”.

Instructions for sorting the data by DATE:

By default, the data is in descending order (Newest to Oldest). You need to re-sort the data so that the oldest date is at the top of your spreadsheet (i.e. highlight DATE columns and click on DATA and then SORT, make sure you “Expand the selection” is selected, then click on SORT and sort by Date and order by ‘Oldest to Newest’).

First Calculate Returns:

Keep only Date and Adj Close columns for each company. Put all three sets of data in

one excel file to do further analysis.

use the ‘Adj Close’ column to obtain returns for each period. Remember that the

Adjusted Close column has already adjusted the prices for dividends and stock splits so

you do not have to adjust for it again. Just use the adjusted close column to obtain the

returns.

1 1 = −

t−

t t P

PR

Solve for the following:

A. Calculate the average return, variance and standard deviation of returns of each series. Comment on the statistics.

B. Calculate the covariance and the correlation coefficient between each of the return series (there should be a total of three correlations and three covariances). Comment on the statistics.

C. If you were to form a portfolio that had 50% of the S&P 500 Index and 50% of Exxon Mobil, what would be the average returns and the standard deviation of that portfolio? (Ignore the fact that both Exxon may already be included in the S&P 500)

D. If you were to add Facebook, Inc to your portfolio so that you now had 33% S&P 500, 33% Exxon Mobile, and 34% Facebook, what would be the new average returns and standard deviation? (Ignore the fact that both Exxon and Facebook may already be included in the S&P 500) Is Facebook a good addition to your portfolio? Why do you think so?

E. Determine Exxon’s beta and Facebook’s beta for the Aug1 2013 – Aug 1, 2016 period. Comment on the statistics.

QUESTION 1

If markets are efficient, the difference between the instrinsic value and the market value of the comapny's security is:

positive

negative

zero

QUESTION 2

Semi-strong-form efficient markets are not weak-form efficient.

True

False

QUESTION 3

Calculate the expected returns of your portfolio

Stock Invest Exp Ret

A $427 3.6%

B $921 16.7%

C $330 28.7%

QUESTION 4

Suppose a stock had an initial price of $69.76 per share, paid a dividend of $8 per share during the year, and had an ending share price of $100.02. What are the percentage returns?

QUESTION 5

Suppose a stock had an initial price of $68.33 per share, paid a dividend of $9.8 per share during the year, and had an ending share price of $81.78. If you own 71 shares, what are the dollar returns?

Note: Enter your answer rounded off to two decimal points. Do not enter $ or comma in the answer box. For example, if your answer is $12.345 then enter as 12.35 in the answer box.

QUESTION 6

You own a portfolio invested 18.5% in Stock A, 12.16% in Stock B, 21.84% in Stock C, and the remainder in Stock D. The beta of these four stocks are 1.14, 0.91, 0.84, and 0.83. What is the portfolio beta?

Note: Enter your answer rounded off to two decimal points. For example, if your answer is 12.345 then enter as 12.35 in the answer box.

QUESTION 7

Based on the following information, calculate the expected returns:

Prob Return

Recession 30% 27.6%

Boom 70% 11.7%

QUESTION 8

A portfolio is invested 48.3% in Stock A, 23.6% in Stock B, and the remainder in Stock C. The expected returns are 16%, 32.3%, and 11.8% respectively. What is the portfolio's expected returns?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 12.345% then enter as 12.35 in the answer box.

QUESTION 9

You own a portfolio invested 17.6% in Stock A, 16.54% in Stock B, 18.22% in Stock C, and the remainder in Stock D. The beta of these four stocks are 0.49, 0.52, 0.44, and 0.85. What is the portfolio beta?

Note: Enter your answer rounded off to two decimal points. For example, if your answer is 12.345 then enter as 12.35 in the answer box

QUESTION 10

Suppose a stock had an initial price of $69.44 per share, paid a dividend of $8.8 per share during the year, and had an ending share price of $97.46. What are the percentage returns?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box

QUESTION 11

Suppose a stock had an initial price of $76.49 per share, paid a dividend of $8 per share during the year, and had an ending share price of $86.38. What are the percentage returns if you own 25 shares?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 12

Suppose a stock had an initial price of $92.58 per share, paid a dividend of $7.9 per share during the year, and had an ending share price of $85.61. What are the dollar returns?

Note: Enter your answer rounded off to two decimal points. Do not enter $ or comma in the answer box. For example, if your answer is $12.345 then enter as 12.35 in the

QUESTION 13

You have observed the following returns on ABC's stocks over the last five years:

3.5%, 8.2%, -13.5%, 12.7%, -2.2%

What is the arithmetic average returns on the stock over this five-year period.

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 14

Suppose the returns for Stock A for last six years was 4%, 7%, 8%, -2%, 9%, and 7%.

Compute the standard deviation of the returns.

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 15

Calculate the expected returns of your portfolio

Stock Invest Exp Ret

A $211 9.1%

B $741 18.6%

C $1,804 22.6%

QUESTION 1

One year ago, you bought a stock for $36.48 a share. You recently received a dividend of $1.62 per share and sold the stock today for $40.18 a share. What is the dollar return and percentage return on this investment?

One year ago, you bought 480 shares of ABC Company for $28.32 a share. You recently received a dividend of $0.75 per share and sold the stock today for $35 a share. What is the dollar return and percentage return on this investment?

QUESTION 2

Over the past five years, a stock provided annual returns of 12.6 percent, 5.8 percent, 7.9 percent, -11.2 percent and -2.4 percent. What is the arithmetic average return? What is the variance of these returns? What is the standard deviation of these returns?

QUESTION 3

Suppose the nominal rate is 15%, the real rate is 10.5%, what is the inflation rate?

If the investors require a 10% real rate of return and the inflation rate is 8%, what is the nominal rate?

The nominal rate is 15.5% and the inflation rate is 5%, what is the real rate?

QUESTION 4

Assume that your portfolio comprises of Stocks A, B, and C. Based on the following information, calculate the portfolio expected return and portfolio beta:

Security Value Return Beta

Stock A $10,000 10% 1.1

Stock B $20,000 5% 0.2

Stock C $30,000 15% 2.1

A stock has a beta of 0.90, the expected return on the market is 13%, and the risk-free rate is 6%. What must the expected return on this stock be?

A stock has an expected return of 17%, the risk-free rate is 5.5%, and the market risk premium is 8%. What must the beta of this stock be?

You own a portfolio that has $1,200 invested in Stock A and $1,900 invested in Stock B. If the expected returns on these stocks are 11% and 16%, respectively, what is the expected return on the portfolio?

QUESTION 6

There are 3 stocks in a portfolio: Stock A, Stock B, and Stock C. The portfolio has a return of 3.3%. You are given below the weight of each stock in the portfolio and rate of return. Calculate the return for Stock C.

Stock Weight Rate of Return

A 35% -4%

B 60% 7%

C 5% ?

QUESTION 11

You want to create a portfolio equally as risky as the market. Given this information, fill in the rest of the following table:

Asset Investment Beta

Stock A 20% 0.8

Stock B 25% 1.3

Stock C ? 1.5

Risk-free Asset ? ?

QUESTION 13

The Treasury Bill rate is 4%, and the expected return on the market portfolio is 12%.

What is the risk premium on the market?

What is the required return on an investment with beta of 1.5?

If the market expects a return of 11.2% from Stock X, what is its beta?

What is the risk premium on the market?

What is the required return on an investment with beta of 1.5?

If the market expects a return of 11.2% from Stock X, what is its beta?

QUESTION 10

Suppose you hold a diversified portfolio consisting of $7,500 investment in each of 20 different common stocks. The portfolio's beta is 1.12. Now, suppose you sell one of the stocks with a beta of 1.0 for $7,500 and use the proceeds to buy another stock whose beta is 1.75. Calculate your portfolio's new beta.

You have a $2 million portfolio consisting of a $100,000 investment in each of 20 different stocks. The portfolio has a beta of 1.1. You are considering selling $100,000 worth of one stock with a beta od 0.90 and using the proceeds to purchase another stock with a beta of 1.40. What will the portfolio's new beta be after these transactions?

QUESTION 8

Assume Risk–free rate = 3%, Expected return on the market = 8%. Calculate the expected return on the stock if the beta is

0

0.5

1

2

Interpret your answers

This assignment will require you to analyze time series of monthly returns. Start by retrieving MONTHLY data for the period of 1 August, 2013 – 1 August, 2016 from Yahoo website for

− S&P 500 Index (ticker: ^GSPC)

− Exxon Mobil Corporation (ticker: XOM)

− Facebook, Inc. (ticker: FB)

Instructions for downloading the data from Yahoo!:

To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click on “Historical Data”. Enter “Time Period” as given above. For “Frequency”, make sure Monthly is selected and then click on “Apply”. Click on “Download Data”.

Instructions for sorting the data by DATE:

By default, the data is in descending order (Newest to Oldest). You need to re-sort the data so that the oldest date is at the top of your spreadsheet (i.e. highlight DATE columns and click on DATA and then SORT, make sure you “Expand the selection” is selected, then click on SORT and sort by Date and order by ‘Oldest to Newest’).

First Calculate Returns:

Keep only Date and Adj Close columns for each company. Put all three sets of data in

one excel file to do further analysis.

use the ‘Adj Close’ column to obtain returns for each period. Remember that the

Adjusted Close column has already adjusted the prices for dividends and stock splits so

you do not have to adjust for it again. Just use the adjusted close column to obtain the

returns.

1 1 = −

t−

t t P

PR

Solve for the following:

A. Calculate the average return, variance and standard deviation of returns of each series. Comment on the statistics.

B. Calculate the covariance and the correlation coefficient between each of the return series (there should be a total of three correlations and three covariances). Comment on the statistics.

C. If you were to form a portfolio that had 50% of the S&P 500 Index and 50% of Exxon Mobil, what would be the average returns and the standard deviation of that portfolio? (Ignore the fact that both Exxon may already be included in the S&P 500)

D. If you were to add Facebook, Inc to your portfolio so that you now had 33% S&P 500, 33% Exxon Mobile, and 34% Facebook, what would be the new average returns and standard deviation? (Ignore the fact that both Exxon and Facebook may already be included in the S&P 500) Is Facebook a good addition to your portfolio? Why do you think so?

E. Determine Exxon’s beta and Facebook’s beta for the Aug1 2013 – Aug 1, 2016 period. Comment on the statistics.

QUESTION 1

If markets are efficient, the difference between the instrinsic value and the market value of the comapny's security is:

positive

negative

zero

QUESTION 2

Semi-strong-form efficient markets are not weak-form efficient.

True

False

QUESTION 3

Calculate the expected returns of your portfolio

Stock Invest Exp Ret

A $427 3.6%

B $921 16.7%

C $330 28.7%

QUESTION 4

Suppose a stock had an initial price of $69.76 per share, paid a dividend of $8 per share during the year, and had an ending share price of $100.02. What are the percentage returns?

QUESTION 5

Suppose a stock had an initial price of $68.33 per share, paid a dividend of $9.8 per share during the year, and had an ending share price of $81.78. If you own 71 shares, what are the dollar returns?

Note: Enter your answer rounded off to two decimal points. Do not enter $ or comma in the answer box. For example, if your answer is $12.345 then enter as 12.35 in the answer box.

QUESTION 6

You own a portfolio invested 18.5% in Stock A, 12.16% in Stock B, 21.84% in Stock C, and the remainder in Stock D. The beta of these four stocks are 1.14, 0.91, 0.84, and 0.83. What is the portfolio beta?

Note: Enter your answer rounded off to two decimal points. For example, if your answer is 12.345 then enter as 12.35 in the answer box.

QUESTION 7

Based on the following information, calculate the expected returns:

Prob Return

Recession 30% 27.6%

Boom 70% 11.7%

QUESTION 8

A portfolio is invested 48.3% in Stock A, 23.6% in Stock B, and the remainder in Stock C. The expected returns are 16%, 32.3%, and 11.8% respectively. What is the portfolio's expected returns?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 12.345% then enter as 12.35 in the answer box.

QUESTION 9

You own a portfolio invested 17.6% in Stock A, 16.54% in Stock B, 18.22% in Stock C, and the remainder in Stock D. The beta of these four stocks are 0.49, 0.52, 0.44, and 0.85. What is the portfolio beta?

Note: Enter your answer rounded off to two decimal points. For example, if your answer is 12.345 then enter as 12.35 in the answer box

QUESTION 10

Suppose a stock had an initial price of $69.44 per share, paid a dividend of $8.8 per share during the year, and had an ending share price of $97.46. What are the percentage returns?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box

QUESTION 11

Suppose a stock had an initial price of $76.49 per share, paid a dividend of $8 per share during the year, and had an ending share price of $86.38. What are the percentage returns if you own 25 shares?

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 12

Suppose a stock had an initial price of $92.58 per share, paid a dividend of $7.9 per share during the year, and had an ending share price of $85.61. What are the dollar returns?

Note: Enter your answer rounded off to two decimal points. Do not enter $ or comma in the answer box. For example, if your answer is $12.345 then enter as 12.35 in the

QUESTION 13

You have observed the following returns on ABC's stocks over the last five years:

3.5%, 8.2%, -13.5%, 12.7%, -2.2%

What is the arithmetic average returns on the stock over this five-year period.

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 14

Suppose the returns for Stock A for last six years was 4%, 7%, 8%, -2%, 9%, and 7%.

Compute the standard deviation of the returns.

Note: Enter your answer in percentages rounded off to two decimal points. Do not enter % in the answer box. For example, if your answer is 0.12345 then enter as 12.35 in the answer box.

QUESTION 15

Calculate the expected returns of your portfolio

Stock Invest Exp Ret

A $211 9.1%

B $741 18.6%

C $1,804 22.6%

QUESTION 1

One year ago, you bought a stock for $36.48 a share. You recently received a dividend of $1.62 per share and sold the stock today for $40.18 a share. What is the dollar return and percentage return on this investment?

One year ago, you bought 480 shares of ABC Company for $28.32 a share. You recently received a dividend of $0.75 per share and sold the stock today for $35 a share. What is the dollar return and percentage return on this investment?

QUESTION 2

Over the past five years, a stock provided annual returns of 12.6 percent, 5.8 percent, 7.9 percent, -11.2 percent and -2.4 percent. What is the arithmetic average return? What is the variance of these returns? What is the standard deviation of these returns?

QUESTION 3

Suppose the nominal rate is 15%, the real rate is 10.5%, what is the inflation rate?

If the investors require a 10% real rate of return and the inflation rate is 8%, what is the nominal rate?

The nominal rate is 15.5% and the inflation rate is 5%, what is the real rate?

QUESTION 4

Assume that your portfolio comprises of Stocks A, B, and C. Based on the following information, calculate the portfolio expected return and portfolio beta:

Security Value Return Beta

Stock A $10,000 10% 1.1

Stock B $20,000 5% 0.2

Stock C $30,000 15% 2.1

A stock has a beta of 0.90, the expected return on the market is 13%, and the risk-free rate is 6%. What must the expected return on this stock be?

A stock has an expected return of 17%, the risk-free rate is 5.5%, and the market risk premium is 8%. What must the beta of this stock be?

You own a portfolio that has $1,200 invested in Stock A and $1,900 invested in Stock B. If the expected returns on these stocks are 11% and 16%, respectively, what is the expected return on the portfolio?

QUESTION 6

There are 3 stocks in a portfolio: Stock A, Stock B, and Stock C. The portfolio has a return of 3.3%. You are given below the weight of each stock in the portfolio and rate of return. Calculate the return for Stock C.

Stock Weight Rate of Return

A 35% -4%

B 60% 7%

C 5% ?

QUESTION 11

You want to create a portfolio equally as risky as the market. Given this information, fill in the rest of the following table:

Asset Investment Beta

Stock A 20% 0.8

Stock B 25% 1.3

Stock C ? 1.5

Risk-free Asset ? ?

QUESTION 13

The Treasury Bill rate is 4%, and the expected return on the market portfolio is 12%.

What is the risk premium on the market?

What is the required return on an investment with beta of 1.5?

If the market expects a return of 11.2% from Stock X, what is its beta?

What is the risk premium on the market?

What is the required return on an investment with beta of 1.5?

If the market expects a return of 11.2% from Stock X, what is its beta?

QUESTION 10

Suppose you hold a diversified portfolio consisting of $7,500 investment in each of 20 different common stocks. The portfolio's beta is 1.12. Now, suppose you sell one of the stocks with a beta of 1.0 for $7,500 and use the proceeds to buy another stock whose beta is 1.75. Calculate your portfolio's new beta.

You have a $2 million portfolio consisting of a $100,000 investment in each of 20 different stocks. The portfolio has a beta of 1.1. You are considering selling $100,000 worth of one stock with a beta od 0.90 and using the proceeds to purchase another stock with a beta of 1.40. What will the portfolio's new beta be after these transactions?

QUESTION 8

Assume Risk–free rate = 3%, Expected return on the market = 8%. Calculate the expected return on the stock if the beta is

0

0.5

1

2

Interpret your answers

You'll get a 117.5KB .ZIP file.