Homework SourseWITHOUT USING ANY EXCEL FUNCTION A CALCULATE THE BETA OF THE
WITHOUT USING ANY EXCEL FUNCTION
A) CALCULATE THE BETA OF THE TWO STOCKS (SHOW FORMULA WORK)
B) ACCORDING TO THESE ESTIMATES, WHICH COMPANY IS RISKIER?
C) HOW DO YOUR BETA ESTIMATES DIFFER FROM THE ONES YOU CAN GET USING THE \"SLOPE\" OR \"LINEST\" FUNCTIONS IN EXCEL? CARRY OUT THOSE COMPUTATIONS AND REPORT THEM HERE
Month Month Month Ended Ended Dec-07 Nov-07 Oct-07 Sep-07 Hug-07 Jul-07 Jun-07 May-07 Apr-07 Mar-07 Feb-07 Monthly Returns (%) Yahoo Google S&P500; -13.241 -0.219 -0.692 -13.794 -1.980 -4.182 15.861 24.632 1.590 18.093 10.096 3.736 -2.237 1.029 1.497 -14.302 -2.430 -3.097 -5.470 4.979 -1.660 2.354 5.628 3.486 -10.387 2.885 4.428 1.393 1.938 1.116 9.007 -10.379 -1.950 10.846 8.908 1.511 Ended Dec-06 Nov-06 Oct-06 Sep-06 Aug-06 Jul-06 Monthly Returns (%) Yahoo Google S&P500; -5.442 -5.018 1.403 2.544 1.767 1.899 4.193 18.534 3.257 .-12.314 6.174 2.575 6.227 -2.087 2.376 -17.758 -7.805 0.616 4.463 12.778 0.133 -3.630 -11.035 -2.875 1.612 7.164 1.342 0.624 7.551 1.244 -6.735 -16.188 0.271 -12.264 4.291 2.648 Dec-05 Nov-05 Oct-05 Sep-05 Aug-05 Jul-05 Jun-05 May-05 Apr-05 Mar-05 Feb-05 Monthly Returns (%) Yahoo Google S&P500; -2.610 2.457 0.036 8.818 8.806 3.778 9.249 17.595 -1.668 1.561 10.650 0.810 -0.060 -0.612 -0.912 -3.781 -2.172 3.717 -6.855 6.088 0.143 7.826 26.032 3.179 1.770 21.877 -1.896 5.051 -1.769 -8.350 -3.900 2.103 -6.555 1.468 -2.437 Jun-06 May-06 -3.979 Apr-06 Mar-06 Feb-06 Jan-06 Jan-05 Jan-07Solution
Beta is calculated throught the following formula:
Standard deviation (X) / Standard deviation (Benchmark) * correlation of X\'s returns with Benchmark\'s
Since the question is not appearing in table format - it appears random text, I have taken an example of how it works. Below is the sample data (please use the calculations in your data):
S.D (X) = 0.027439
S.D (S&P 500) = 0.026215
R (correlation) = 0.27733
Beta = 0.290275
If you apply a function of =SLOPE(X returns, S&P returns) or =LINEST(X returns, S&P returns,1,0), the answer is same, 0.290275. Similar calculations apply to stock Y.
The one with higher beta will be riskier - beta represents the risk of the security compared to the benchmark returns. Higher the beta, more risky is the company.
| X | Y | S&P |
| 4.21% | 3.32% | 9.05% |
| 9.14% | 3.25% | 6.89% |
| 9.43% | 0.90% | 8.12% |
| 3.05% | 6.51% | 1.91% |
| 2.17% | 5.94% | 3.09% |
| 5.56% | 1.57% | 5.10% |
| 4.78% | 6.28% | 4.53% |
| 9.93% | 5.46% | 7.03% |
| 6.52% | 7.71% | 7.31% |
| 3.39% | 7.16% | 7.01% |
| 3.77% | 2.68% | 1.13% |
| 1.65% | 3.61% | 7.48% |
| 3.66% | 8.54% | 2.96% |
| 4.93% | 1.69% | 8.39% |
| 7.16% | 3.68% | 8.89% |
| 5.52% | 4.69% | 3.39% |
| 8.83% | 0.78% | 7.53% |
| 6.44% | 4.72% | 1.12% |
| 2.11% | 9.40% | 6.20% |
| 5.71% | 4.59% | 7.37% |
| 5.75% | 5.39% | 5.24% |
| 2.76% | 2.25% | 1.43% |
| 4.74% | 2.05% | 5.57% |
| 0.53% | 2.19% | 1.84% |
| 4.74% | 6.35% | 9.25% |
| 9.67% | 2.51% | 7.69% |
| 9.94% | 8.30% | 5.97% |
| 5.78% | 5.46% | 2.34% |
| 7.00% | 2.14% | 7.27% |
| 6.51% | 5.68% | 3.52% |
| 9.67% | 8.50% | 7.99% |
| 5.48% | 0.84% | 5.68% |
| 9.70% | 4.49% | 1.18% |
| 4.62% | 8.26% | 6.61% |
| 0.91% | 4.16% | 1.98% |
| 1.08% | 3.33% | 9.07% |
