Part 1: Calculation of the Returns for the Market Index and Your Stock

Use this Word file as the pro forma into which you incorporate your answers. You will find tables with labels but no results – in answering your assignment you will need to fill these tables. 

In the Excel spreadsheet “cw data.xlsx” you will find data to be used for your assignment.

In column B you will see the value of the S&P500 index (the market index).

In column C you have the monthly yield for US Treasury Bills that is viewed as the risk-free rate of return.

In columns D through to VN you will see price data for stocks listed on the US stockmarkets.

Each of you will be using data for one of the stocks along with the market index and the Treasury Bill yield to complete this assignment. In row 1 you will see a list of registration numbers – the stock you will be using for your assignment is in the column with your registration number so you first need to locate your registration number. An easy way of doing this is to use the ‘Find’ facility in Excel. Make sure you have the “Home” tab selected, look to the far right of the menu bar and you should see a picture of binoculars titled “Find &Select” – click on this, type your registration number and press return until you find thecolumn headed by your registration number. 

Tips and guidance:

First select and copy your data (the price data for the stock, the market index, and the Treasury Bill yield) into a separate Excel file within which you can undertake the necessary calculations.

Essential: You must report your answers in the tables and spaces provided below. Be sure to clearly explain your results and where appropriate use the equation editor to present any formula or equations that you find necessary – you will find this on the “Insert” tab towards the far right of the menu bar. 

For Part 1, you can make full use of functions within Excel to calculate the statistics. For Part 2, you are advised to use the regression facility in data analysis which you should find on the “Data” tab.For Part 3, you will make use of information from Bloomberg.

If you haven’t got data analysis on your “Data” tab, see this short video for how to enable it:https://www.youtube.com/watch?v=6nCP65Nbm0E (Excel 2007)

https://youtu.be/DoAckgj-jUk (Excel 2010)

https://www.youtube.com/watch?v=fuhR8tG7QRs (Excel 2013)

https://www.youtube.com/watch?v=mIoS7IRo36c (Excel 2016)

When you have completed your work upload both the Word file and the Excel file to FASER.

We strongly recommend that you upload before this date. Remember, there is a zero tolerance policy for late submission so it is good practice to save a draft of your work, even if it is partially complete, to FASER a few days before the deadline which you can save over with the final version once you have completed the work. By doing this, should you have any last minute unforeseen accidents (e.g., computer failures) then you have some work that we can mark. 

Name:

Registration number:

PART 1(20 marks)

The first thing you need to do is calculate the returns for the market index and your stock. Note: The Treasury Bill yield is a rate of return so you do not need to calculate this (for example, the value of 0.0249% in December 2015 means that the monthly yield or rate of return on 3-month Treasury Bills was 0.0249%).To calculate the returns for the stock and index you simply calculate the percentage change in price month by month. Note:This ignores dividends but this is fine for our purposes. The important thing is to calculate these returns on a consistent basis with the Treasury Bill yield which is monthly. Since there are 33 months of price data you should have 32 months of returns. Calculate the average return and standard deviation for the returns of your stock, the index, and the Treasury Bill and insert the results in Table 1. Choose an appropriate number of decimal places in order to make the results are easy on the eye.

Table 1

Market

Your stock

[T ROWE PRICE GROUP INC]

Treasury Bill yield

Average

0.82%

0.06%

0.005%

Standard deviation

0.031269

0.046268

6.57247E-05

Complete the correlation matrix in Table 2. To do this you might find the Excel function “CORREL” useful. Present the correlations using two decimal places. 

Table 2

Market

Your stock

Market

1

0.88

Your stock

0.88

1

Think about the results in Tables 1 and 2 and provide brief comments regarding the following several aspects: (1)Look at the standard deviation values between your stock and Treasury Bills. What do they indicate? (2)What’s the correlation between the S&P 500 index and your stock? (3)How do you feel about the sensitivity of your security’s returns to those of the market? (max 200 words)

1) The standard deviation of the stock is 4.63 indicating a variation in the returns. On the contrary the standard deviation of the Treasury bill is only 0.007. This indicates that the variation in T-bill is extremely low as compared to the stock. This is perfectly understandable as lower is the the standard deviation lower is the risk associated with the asset class. From this perspective the returns of T-bills are less risky and safer.

2) The correlation between the stock and the market index is highly positive and significant.

3) The correlation provides that the stock is highly sensitive to that of the market. There are 88% chances that stock will movie in the same direction as the market would move.

PART 2(50 marks)

Estimate the CAPM for your stock. The formula for the CAPM is:

You will use the regression tool in Excel to estimate the CAPM. First you need to rearrange the equation by subtracting the risk-free rate from both sides and allow for an intercept which we call alpha (a) giving the empirical version of the CAPM:

In order to estimate this regression you first need to construct the variables. You create the y-variable (also known as the dependent variable) by subtracting the risk-free rate (the Treasury Bill yield) from the returns of your stock and you create the x-variable (the independent variable) by subtracting the risk-free rate from the return on the market (the S&P500). The regression tool will enable you to estimate the values of alpha (the intercept) and the beta (the slope coefficient). 

2.1. Insert a scatter plot chart below using your stock risk premium as the y-axis and market risk premium as the x-axis (Hint: Option under the ‘Insert’ tab in Excel). Then add a trendline.

2.2. Record the essential information from the regression in Table 3.

Table 3

Your stock

Intercept (a) coefficient

-0.01016

Intercept standard error

0.003935

Slope (b) coefficient

1.315467

Slope standard error

0.12344

R2

0.791037

n (sample size)

32

Conduct hypotheses tests at a 5% significance level on the intercept and slope coefficient to see if the intercept is significantly different from zero and the slope coefficient is significantly different from one (Hint: Be sure to clearly present the null and alternative hypotheses, the test statistic, critical values)(max 150 words)

N ull Hypothesis- Intercept is not significantly different from zero

Alternative Hypothesis - Intercept is significantly different from zero

t Stat

0.768401

P(T<=t) one-tail

0.222798

t Critical one-tail

1.673565

P(T<=t) two-tail

0.445596

t Critical two-tail

2.004879

We do a two-tail test (inequality). lf t Stat < -t Critical two-tail or t Stat > t Critical two-tail, we reject the null hypothesis. This is not the case, -2.004 < 0.768 < 2.004. Therefore, we do not reject the null hypothesis. The intercept is not significantly different from zero.

Interpret the intercept and slope coefficients – what are they telling us? (max 150 words)

The slope coefficient tells us that a 1% change in the market will lead to 1.31% change in the price of the share of the company.

The intercept tells us that if there is 0% move in the market direction, the share price of the stock would reduce by -0.01%.

Interpret the R2

of the regression – what is it telling us? Try to combine with the above chart to explain. (max 100 words)

Combining the R square value with the chart it could be argued that the model explains 79% variability of the response data around its mean. Greater would be the R square the greater would the model fits the data. However R square comes with its own limitations which must be understood before relying on the results.

PART 3(30 marks)

Find your stock on Bloomberg. Hint: Be careful to choose the equity of the company and not some other debt instrument; also, make sure it is listed on the US stock markets as some companies can be cross-listed on stock markets of other countries. Use the “ANR” function to find information on analyst recommendations. Save a screenshot and insert into this file and then provide a commentary.

Comment on the analyst recommendations. Each of you is looking at the recommendations for a different stock so there is no one way of answering this question. We suggest you focus on whatever is striking about the recommendations for your stock. You may want to consider: the degree of consensus or disagreement; the change in recommendations over time; the comparison of target and actual price; etc. If it helps your discussion, you can include an additional Bloomberg screenshot if relevant, but you must stay within the word count. (max 200 words)

As the Bloomberg terminal was not available the reliance was made on another authentic website the wall street journal. The analyst ratings suggest that 27% of the analyst believe that the stock price is expected to increase in the near future. 11% of the analyst believe that stock is overvalued and should be sold. 61% of the analyst believes that stock must be hold for now. Furthermore the analyst ratings in the past have been lower than the actual result of the company. This provides that the actual performance of the company could be expected to be better than analysts’ recommendations in the future.