To determine:
To set up the spreadsheet of the given data as per the Example 6.3 and to find the beta of Apple
Introduction:
The net loss or the net gain on any investment over a certain time period which is expressed as the investment's initial cost percentage is the

Answer to Problem 22PS
The beta is 0.87
Explanation of Solution
Given Information:
The data is available in Connect. It shows a data for five year period.
From the Connect, the data is obtained as below:
S&P | RF (T-bill) | Excess returns | ||||
Month | Return | Return | Return | Month | S&P | |
Jan-04 | 1.35 | 0.56 | 0.07 | Jan-04 | 0.49 | 1.28 |
Feb-04 | -1.32 | 0.00 | 0.06 | Feb-04 | -0.06 | -1.38 |
Mar-04 | -1.89 | -3.19 | 0.09 | Mar-04 | -3.28 | -1.98 |
Apr-04 | 1.70 | 0.00 | 0.08 | Apr-04 | -0.08 | 1.62 |
May-04 | 1.85 | -0.25 | 0.06 | May-04 | -0.31 | 1.79 |
Jun-04 | -3.22 | 2.39 | 0.08 | Jun-04 | 2.31 | -3.30 |
Jul-04 | 0.25 | 1.69 | 0.1 | Jul-04 | 1.59 | 0.15 |
Aug-04 | 1.00 | 1.51 | 0.11 | Aug-04 | 1.40 | 0.89 |
Sep-04 | 1.29 | 0.08 | 0.11 | Sep-04 | -0.03 | 1.18 |
Oct-04 | 4.46 | -1.17 | 0.11 | Oct-04 | -1.28 | 4.35 |
Nov-04 | 3.01 | 1.97 | 0.15 | Nov-04 | 1.82 | 2.86 |
Dec-04 | -2.24 | -3.02 | 0.16 | Dec-04 | -3.18 | -2.40 |
Jan-05 | 2.09 | -0.24 | 0.16 | Jan-05 | -0.40 | 1.93 |
Feb-05 | -1.83 | -15.28 | 0.16 | Feb-05 | -15.44 | -1.99 |
Mar-05 | -1.87 | -7.84 | 0.21 | Mar-05 | -8.05 | -2.08 |
Apr-05 | 3.22 | 2.25 | 0.21 | Apr-05 | 2.04 | 3.01 |
May-05 | 0.15 | 4.91 | 0.24 | May-05 | 4.67 | -0.09 |
Jun-05 | 3.82 | 5.44 | 0.23 | Jun-05 | 5.21 | 3.59 |
Jul-05 | -0.93 | -8.88 | 0.24 | Jul-05 | -9.12 | -1.17 |
Aug-05 | 0.80 | -5.37 | 0.3 | Aug-05 | -5.67 | 0.50 |
Sep-05 | -2.36 | -7.77 | 0.29 | Sep-05 | -8.06 | -2.65 |
Oct-05 | 4.39 | -7.52 | 0.27 | Oct-05 | -7.79 | 4.12 |
Nov-05 | -0.19 | -4.31 | 0.31 | Nov-05 | -4.62 | -0.50 |
Dec-05 | 2.41 | 12.61 | 0.32 | Dec-05 | 12.29 | 2.09 |
Jan-06 | 0.57 | -3.43 | 0.35 | Jan-06 | -3.78 | 0.22 |
Feb-06 | 1.65 | 6.04 | 0.34 | Feb-06 | 5.70 | 1.31 |
Mar-06 | 1.26 | 1.23 | 0.37 | Mar-06 | 0.86 | 0.89 |
Apr-06 | -3.01 | 6.39 | 0.36 | Apr-06 | 6.03 | -3.37 |
May-06 | 0.26 | 5.18 | 0.43 | May-06 | 4.75 | -0.17 |
Jun-06 | 0.45 | 4.63 | 0.4 | Jun-06 | 4.23 | 0.05 |
Jul-06 | 2.18 | -1.41 | 0.4 | Jul-06 | -1.81 | 1.78 |
Aug-06 | 2.70 | 6.11 | 0.42 | Aug-06 | 5.69 | 2.28 |
Sep-06 | 3.16 | 3.60 | 0.41 | Sep-06 | 3.19 | 2.75 |
Oct-06 | 1.99 | 0.61 | 0.41 | Oct-06 | 0.20 | 1.58 |
Nov-06 | 0.77 | 0.52 | 0.42 | Nov-06 | 0.10 | 0.35 |
Dec-06 | 1.51 | 6.70 | 0.4 | Dec-06 | 6.30 | 1.11 |
Jan-07 | -1.96 | -0.16 | 0.44 | Jan-07 | -0.60 | -2.40 |
Feb-07 | 1.16 | -0.73 | 0.38 | Feb-07 | -1.11 | 0.78 |
Mar-07 | 4.42 | -0.08 | 0.43 | Mar-07 | -0.51 | 3.99 |
Apr-07 | 3.39 | 1.87 | 0.44 | Apr-07 | 1.43 | 2.95 |
May-07 | -1.46 | -0.24 | 0.41 | May-07 | -0.65 | -1.87 |
Jun-07 | -3.13 | -6.32 | 0.4 | Jun-07 | -6.72 | -3.53 |
Jul-07 | 1.28 | -4.78 | 0.4 | Jul-07 | -5.18 | 0.88 |
Aug-07 | 3.88 | 8.79 | 0.42 | Aug-07 | 8.37 | 3.46 |
Sep-07 | 1.35 | -0.25 | 0.32 | Sep-07 | -0.57 | 1.03 |
Oct-07 | -3.87 | -6.86 | 0.32 | Oct-07 | -7.18 | -4.19 |
Nov-07 | -1.13 | -5.59 | 0.34 | Nov-07 | -5.93 | -1.47 |
Dec-07 | -6.04 | 11.18 | 0.27 | Dec-07 | 10.91 | -6.31 |
Jan-08 | -2.58 | -3.13 | 0.21 | Jan-08 | -3.34 | -2.79 |
Feb-08 | -0.90 | -9.60 | 0.13 | Feb-08 | -9.73 | -1.03 |
Mar-08 | 4.77 | 8.78 | 0.17 | Mar-08 | 8.61 | 4.60 |
Apr-08 | 1.51 | -6.48 | 0.17 | Apr-08 | -6.65 | 1.34 |
May-08 | -8.35 | -15.56 | 0.17 | May-08 | -15.73 | -8.52 |
Jun-08 | -0.90 | -11.01 | 0.17 | Jun-08 | -11.18 | -1.07 |
Jul-08 | 1.55 | -4.92 | 0.15 | Jul-08 | -5.07 | 1.40 |
Aug-08 | -9.42 | -29.61 | 0.12 | Aug-08 | -29.73 | -9.54 |
Sep-08 | -16.52 | -13.77 | 0.15 | Sep-08 | -13.92 | -16.67 |
Oct-08 | -6.96 | -26.04 | 0.08 | Oct-08 | -26.12 | -7.04 |
Nov-08 | 0.99 | -25.44 | 0.02 | Nov-08 | -25.46 | 0.97 |
Dec-08 | -8.22 | 23.02 | 0.09 | Dec-08 | 22.93 | -8.31 |
AVERAGE(F6:F65) | AVERAGE(G6:G65) | |||||
-2.12 | -0.55 | |||||
Covariance matrix | ||||||
S&P return | CORREL(F6:F65, G6:G65) | |||||
0.3716121 | ||||||
Regression Statistics | ||||||
Multiple R | 0.37 | |||||
R Square | B78*B78 | 0.1369 | ||||
Adjusted R Square | 0.12 | |||||
Standard Error | 8.32 | |||||
Observation | 60 | |||||
Coefficients | Standard Error | t Stat | P-value | P-value | ||
Intercept | -1.65 | 1.08 | -1.52 | D85-B85 | 0.13 | |
Spy | 0.87 | 0.28 | 3.05 | 0 | ||
The Excel calculations given above give the price movement of S&P index and Apple. To get the covariance, the average of the excess returns is calculated.
Excel analysis is used on the given spreadsheet and the data is obtained from that.
From this, 0.37 is obtained as the multiple regressions. The R-Square is obtained as 0.14. The standard error value is 8.32.
Therefore, the beta is 0.87.
Want to see more full solutions like this?
Chapter 6 Solutions
CONNECT WITH LEARNSMART FOR BODIE: ESSE
- Muskoka Tourism has announced a rights offer to raise $30 million for a new magazine, titled ‘Discover Muskoka’. The magazine will review potential articles after the author pays a nonrefundable reviewing fee of $5,000 per page. The stock currently sells for $52 per share and there are 3.9 million shares outstanding. Required What is the maximum possible subscription price? What is the minimum? If the subscription price is set at $46 per share, how many shares must be sold? How many rights will it take to buy one share? What is the ex-rights price? What is the value of a right?arrow_forwardNorthern Escapes Inc. has 225,000 shares of stock outstanding. Each share is worth $73, so the company’s market value of equity is $16,425,000. Suppose the firm issues 30,000 new shares at the following prices: $73, $69, and $60. What will the effect be of each of these alternative offering prices on the existing price per share?arrow_forwardNeed answer correctly.arrow_forward
- Muskoka Tourism has announced a rights offer to raise $30 million for a new magazine, titled ‘Discover Muskoka’. The magazine will review potential articles after the author pays a nonrefundable reviewing fee of $5,000 per page. The stock currently sells for $52 per share and there are 3.9 million shares outstanding.arrow_forwardSs stores probarrow_forwardHenrietta’s Pine Bakery Corporation would like to raise $75 million to finance its expansion into new markets. The company will sell new shares of equity via a general cash offering to raise the needed funds. If the offer price is $15 per share and the company’s underwriters charge a 6% spread, how many shares need to be sold?arrow_forward
- Essentials Of InvestmentsFinanceISBN:9781260013924Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.Publisher:Mcgraw-hill Education,
- Foundations Of FinanceFinanceISBN:9780134897264Author:KEOWN, Arthur J., Martin, John D., PETTY, J. WilliamPublisher:Pearson,Fundamentals of Financial Management (MindTap Cou...FinanceISBN:9781337395250Author:Eugene F. Brigham, Joel F. HoustonPublisher:Cengage LearningCorporate Finance (The Mcgraw-hill/Irwin Series i...FinanceISBN:9780077861759Author:Stephen A. Ross Franco Modigliani Professor of Financial Economics Professor, Randolph W Westerfield Robert R. Dockson Deans Chair in Bus. Admin., Jeffrey Jaffe, Bradford D Jordan ProfessorPublisher:McGraw-Hill Education





