a. Calculate returns for these three series in Excel using the transformation: rt = 100 ln[Pt/Pt1]
Hints:
• We performed a similar task in Tutorial 01.
• These numbers would represent percentages after multiplication with 100 in the formula above. However,you would not put a percentage sign in your data. For example, returns for two periods are 0.35% and 0.41% but we omit % sign in our excel worksheet and use 0.35 and 0.41.
b. Obtain the summary statistics for your sample and briefly discuss the risk and average return relationship in each stock. Which stock (Google or Yahoo) is relatively riskier than the other
b. What is the probability of observing average return of at least 4% in both stocks
c. What is the likelihood of loss in a sample of 36 periods for both stocks
Before investing in one of the two stocks based on higher riskreturn relationship, you further want to determine whether both stocks have same population average return. Perform an appropriate hypothesis test using information in your actual sample of 56 observations and report your findings. Also, which stock will you prefer and why
Create two new columns in Excel for excess return on your preferred stock (yt) and excess market return (xt) by subtracting the 10year TBill rate from both series as follows.
Excess return on your preferred stock: yt = rt  rf,t
Excess return on market: xt = rM,t  rf,t
a. Estimate the CAPM using linear regression where the dependent variable is excess return on your preferred stock while the independent variable is excess market return (computed as return on S&P 500 minus the risk fee rate) and report your results.
b. Interpret the estimated coefficients in relation to the profitability of the Stock and its riskiness in comparison with the market.
c. Interpret the value of R2
d. Interpret 95% confidence interval for the slope coefficient.
Answer:
The above line graph shows the standard and poor index for 500 index time series for the time between October 2009 and May 2014. There is a generally increasing trend showing that companies' index improved significantly between these two time frames. However, the time between April 2012 and May 2014 seems to have increased in larger rate compared to the previous time. The index data did not have a lot of seasonal effects because there is no a lot of variations as observed in the time series chart.
The line graph in figure 2 shows that yahoo stock has an approximate unchanging growth between May 2009 and August 2012. This indicates there might be business problems that persisted during this time frame, hence affecting the growth of the Yahoo stock index. During all this time, the index was revolving around 15 USD, which then increased after August to a maximum of around 40 in January 2014. Therefore, between September 2012 and January 2014, there was an increasing trend for Yahoo Index and this might have attracted a lot of cus
tomers to invest. After January 2014, Yahoo Index seems to have experienced a decreasing trend, hence affecting the profitability of the business to the investment.
Between October 2009 and August 2010, the Google Price index experienced a decreasing trend from around 570 US dollars to approximately 420 USD dollar. This was a great drop in the investment which might have reduced the rate of investment for the stock. After August 2010, the stock price improved to around 600 USD in October 2010, which approximately remained in that level until February 2011. The price reduced to around 500 USD and it experienced seasonality in around June and July 2011. However, the seasonal effects persisted until June 2012, where the stock price increased significantly from June 2012 to February 2014, marking a maximum stock price of around 1200 US dollars between October 2009 and May 2014.
In comparison between the three stock prices, S & P seems to be have experienced minimum seasonal effects for the trend. In addition, its trend has been constantly increasing from October 2009 to May 2014, although it experienced some significant drop between April 2012 and October the same year. After this challenging period for S & P stock price index, it improved significantly from around 1100 US dollars to 1900 US dollars in May 2014. Google and Yahoo stock prices experienced constant and unchanging prices for longer periods, indicating that they experienced slowed growth in the market and investment.
1. Summary Statistics
a. Determining the stock that has more risk

S&P 500 Index 
Google Stock Price 
Yahoo stock Price 
US TN (10 years) 
Rt  S & P 
Rt  Google 
Rt  Yahoo 
Mean 
1382.69 
691.15 
19.93 
2.56 
1.09 
1.25 
1.48 
Standard Error 
32.60 
26.79 
1.04 
0.09 
0.53 
1.05 
1.02 
Median 
1345.20 
613.40 
16.40 
2.61 
1.76 
1.28 
1.69 
Standard Deviation 
241.76 
198.70 
7.69 
0.68 
3.90 
7.76 
7.57 
Sample Variance 
58445.77 
39482.73 
59.13 
0.46 
15.18 
60.15 
57.29 
Kurtosis 
0.59 
0.51 
0.81 
1.12 
0.23 
0.50 
0.32 
Skewness 
0.57 
1.19 
1.46 
0.14 
0.40 
0.06 
0.17 
Range 
853.24 
770.70 
27.34 
2.35 
18.78 
33.27 
33.95 
Minimum 
1030.71 
444.95 
13.10 
1.49 
8.55 
15.69 
13.81 
Maximum 
1883.95 
1215.65 
40.44 
3.84 
10.23 
17.58 
20.14 
Sum 
76048.09 
38013.41 
1096.17 
140.71 
59.78 
68.98 
81.58 
Count 
55 
55 
55 
55 
55 
55 
55 
The table above shows the summary statistics of three stock prices, S & P, Google and Yahoo together with their adjacent returns. The returns can be used to determine the riskier stock and the best within the three. Yahoo stock price has the highest average returns of 1.48% followed by Google stock that has 1.25% and finally S & P stock price that has an average of 1.09%. Comparing the three stocks, Google has the highest variance of 60.15% against S & P stock that has 15% variation. Therefore, it can be concluded that Google stock price has the most investment risk followed by Yahoo and finally S & P Index. S & P is the best stock to invest for individuals who does not wish to be involved in highrisk business, although stock with great risk has the highest returns on average. Comparing Google and Yahoo Index, the former is riskier than Yahoo because it has more variation.
b. Normality test
Testing normality for the three returns; S & P, Google and Yahoo, the test statistics will be obtained and the QQ plot will be plotted to determine whether the data is normally distributed.

Rt  S & P 
Rt  Google 
Rt  Yahoo 
W 
0.978788 
0.979012 
0.990743 
pvalue 
0.437831 
0.446812 
0.94758 
alpha 
0.05 
0.05 
0.05 
normal 
yes 
yes 
yes 
Based on the table above, the three data for the stock price returns are normally distributed. This test is based on the following hypothesis.
Null hypothesis: The data is normally distributed
Since the observed pvalues are greater than the significance level 0.05, we fail to reject the null hypothesis and conclude that the data is normally distributed. The argument can also be observed on the QQ plot plots shown below for the three variables.
The points are concentrated within the straight line indicating that they are normally distributed.After standardising the data, most of the points are concentrated in the straight line, which is a clear indication that the data is normally distributed which also applies to Yahoo stock returns data.
3. Testing whether Google and Yahoo have the same average returns
Mean 
1.2542 
Known Variance 
60.15 
Observations 
55 
Hypothesized Mean Difference 
0 
z 
0.1568 
P(Z<=z) onetail 
0.4377 
z Critical onetail 
1.6449 
P(Z<=z) twotail 
0.8754 
z Critical twotail 
1.9600 
Null hypothesis: The average of Google and Yahoo are different
Alternative hypothesis: Google and Yahoo returns averages are the same
Based on the twosample test shown above, the pvalue is greater than the significance level, hence failing to reject the null hypothesis. Therefore, I would prefer to use Google stock as the investment stand because it has a greater chance of making at least 4% returns and a lower chance of making losses.
4. Calculating Excess returns
US TN (10 years) 
Rt  S & P 
Rt  Google 
Rt  Yahoo 
Yt 
Xt 
3.392 
0.000 
0.000 
0.000 
3.392 
3.392 
3.201 
5.578 
8.383 
6.027 
5.182 
2.377 
3.843 
1.761 
6.150 
11.414 
2.307 
2.082 
3.609 
3.768 
15.692 
11.147 
19.301 
7.377 
3.595 
2.811 
0.594 
1.979 
4.189 
0.784 
3.833 
5.713 
7.375 
7.667 
3.542 
1.880 
3.663 
1.465 
7.584 
0.000 
11.247 
2.198 
3.301 
8.553 
7.928 
7.471 
11.229 
11.854 
2.951 
5.539 
8.749 
10.290 
11.700 
8.490 
2.907 
6.652 
8.588 
0.289 
5.681 
3.745 
2.477 
4.861 
7.455 
5.707 
9.932 
7.338 
2.517 
8.393 
15.561 
7.775 
13.044 
5.876 
2.612 
3.619 
15.460 
15.163 
12.848 
1.007 
2.797 
0.229 
9.926 
4.148 
12.723 
3.026 
3.305 
6.326 
6.658 
4.993 
3.353 
3.021 
3.378 
2.239 
1.070 
3.115 
2.308 
1.139 
3.414 
3.146 
2.149 
1.722 
1.265 
0.268 
3.454 
0.105 
4.440 
1.693 
7.894 
3.559 
3.296 
2.810 
7.548 
5.935 
10.844 
0.486 
3.050 
1.359 
2.811 
6.718 
5.861 
4.409 
3.158 
1.843 
4.374 
9.567 
7.532 
5.001 
2.805 
2.171 
17.577 
13.810 
14.772 
4.976 
2.218 
5.847 
10.972 
3.819 
13.190 
8.065 
1.924 
7.447 
4.910 
3.286 
6.834 
9.371 
2.175 
10.231 
14.034 
17.189 
11.859 
8.056 
2.068 
0.507 
1.133 
0.447 
0.935 
2.575 
1.871 
0.850 
7.473 
2.638 
5.602 
1.021 
1.799 
4.266 
10.743 
4.178 
12.542 
2.467 
1.977 
3.979 
6.368 
4.225 
4.391 
2.002 
2.216 
3.085 
3.651 
2.596 
1.435 
0.869 
1.915 
0.753 
5.842 
2.081 
7.757 
2.668 
1.581 
6.470 
4.047 
1.949 
5.628 
8.051 
1.659 
3.879 
0.136 
3.798 
1.795 
2.220 
1.492 
1.252 
8.727 
0.063 
7.235 
0.240 
1.562 
1.957 
7.913 
7.810 
6.351 
0.395 
1.637 
2.395 
9.651 
8.690 
8.014 
0.758 
1.686 
1.999 
10.352 
5.242 
12.038 
3.685 
1.606 
0.284 
2.622 
10.850 
1.016 
1.322 
1.756 
0.704 
1.282 
5.846 
0.474 
1.052 
1.985 
4.920 
6.606 
1.366 
4.621 
2.935 
1.888 
1.100 
5.848 
8.212 
3.960 
0.788 
1.852 
3.536 
0.879 
9.910 
2.731 
1.684 
1.675 
1.792 
3.754 
4.974 
2.079 
0.117 
2.164 
2.055 
5.503 
6.155 
3.339 
0.109 
2.478 
1.511 
1.045 
4.551 
1.433 
3.989 
2.593 
4.828 
0.835 
11.135 
1.758 
2.235 
2.749 
3.180 
4.711 
3.514 
7.460 
5.929 
2.615 
2.932 
3.368 
20.137 
0.753 
0.317 
2.542 
4.363 
16.261 
0.696 
13.719 
1.821 
2.741 
2.766 
2.776 
11.569 
0.035 
0.025 
3.026 
2.329 
5.608 
8.944 
2.582 
0.697 
2.668 
3.623 
5.237 
11.602 
2.569 
6.291 
2.658 
4.221 
2.894 
7.127 
0.236 
1.563 
2.723 
0.691 
8.686 
7.433 
11.409 
2.032 
2.648 
0.618 
4.198 
0.139 
6.846 
2.030 

Estimating the CAPM linear regression

Coefficients 
Standard Error 
t Stat 
Pvalue 
Lower 95% 
Upper 95% 
Intercept 
0.5121 
0.9015 
0.5680 
0.5724 
1.2961 
2.3203 
Xt 
1.2343 
0.2163 
5.7064 
0.0000 
0.8005 
1.6682 
The intercept coefficient indicates that in cases where the excess market return is zero, the mean excess return on Google stock will be 0.5121%. Therefore, increasing the excess market return by 1% percent improves Google’s excess returns by 1.2343. Therefore, the market return more risky than Google returns because it contributes to the increment of excess returns for Google stock.

R^{2}interpretation
The predictor variable (market return) was significant at 95% confidence level. Therefore, it contributes 38.06% of the total variation experienced on the Google’s excess returns. This can be observed in the table below, which the regression summary.
Regression Statistics 

Multiple R 
0.6169 
R Square 
0.3806 
Adjusted R Square 
0.3689 
Standard Error 
6.2552 
Observations 
55 
Slope coefficient
The confidence interval for the slope is [0.8005, 1.668], which means that at 95% confidence level, the slope coefficient will always be contained in the interval provided that the data meets the same standards.
6. Determining whether Google stock was neutral
The table above shows the lower and upper bounds of the confidence interval for testing whether Google stock returns are neutral. In this case, the test determines if the Google stock returns that are greater than 0 accounts for 50% of the data. The obtained bounds are [0.4497, 0.7140], indicating that at 95% confidence interval, the proportion of Google returns above 0 will always be with the interval.
This problem has been solved.
Cite This work.
To export a reference to this article please select a referencing stye below.
Urgent Homework (2022) . Retrive from https://www.urgenthomework.com/samplehomework/bus5sbfstatisticsforbusinessandfinancestatisticalmetrics
"." Urgent Homework ,2022, https://www.urgenthomework.com/samplehomework/bus5sbfstatisticsforbusinessandfinancestatisticalmetrics
Urgent Homework (2022) . Available from: https://www.urgenthomework.com/samplehomework/bus5sbfstatisticsforbusinessandfinancestatisticalmetrics
[Accessed 03/10/2022].
Urgent Homework . ''(Urgent Homework ,2022) https://www.urgenthomework.com/samplehomework/bus5sbfstatisticsforbusinessandfinancestatisticalmetrics accessed 03/10/2022.
Follow Us