The attached Excel sheet contains data on annual returns on IBM and 3M stocks ov

Question

The attached Excel sheet contains data on annual returns on IBM and 3M stocks over the period 1990-2002, as well as annual returns on S&P 500 Index and one-year U.S. Treasury bills for this period. You are asked to evaluate performance of IBM and 3M stocks using the CAPM’s Security Market Line with S&P 500 Index as proxy for the market return.

(i) Compute the annual excess returns of IBM, 3M, and the S&P 500.

(ii) Use Excel to compute regression coefficients of IBM excess returns on S&P 500 excess returns. More precisely, use Excel functions SLOPE and INTERCEPT to compute the slope and the intercept of the regression. The slope is the estimate of IBM’s beta. If the intercept is positive, IBM stock has “overper- formed”. Otherwise, it has “underperformed”. Which way is it? Use RSQ

function to find the regression’s R2.

(iii) Do the same calculations for 3M stock as for IBM in part (ii). How has 3M

stock performed over the time period 1990-2002?

Annual returns on S&P 500 Index, IBM stock, 3M stock, and 1-year US T-bill rates. S&P 500 is a proxy

for market return; T-bill rates are for risk-free rates

in respective years.

    Date S&P 500 IBM 3M 1-year T-bill Dec-90 -3.1 17.46 11.3 7.89 Dec-91 30.47 -23.87 14.04 5.86 Dec-92 7.62 -56.85 8.81 3.89 Dec-93 10.08 11.4 10.85 3.43 Dec-94 1.32 26.33 1.49 5.32 Dec-95 37.58 21.75 25.03 5.94 Dec-96 22.96 50.59 25.04 5.52 Dec-97 33.36 32.28 1.2 5.63 Dec-98 28.58 56.67 -11.7 5.05 Dec-99 21.04 15.71 34.43 5.08 Dec-00 -9.1 -23.83 20.79 6.11 Dec-01 -11.89 35.28 -1.92 3.49 Dec-02 -22.1 -44.11 4.22 2

Annual returns on S&P 500 Index, IBM stock, 3M stock, and 1-year US T-bill rates. S&P 500 is a proxy

for market return; T-bill rates are for risk-free rates

in respective years.

Explanation / Answer

Solution:

a. The annual excess return of IBM, 3M and S& P 500 is the respective return less 1-year T-bill.

b.

Slope = 0.724329, Intercept = -0.42404

Since the slope is 0.724329 which is positive, therefore it has over-performed.

R-square is 0.150077.

iii.

Since the slope is 0.108348 is positive, the stock has overperformed.

Date S&P 500 IBM 3M 1-year T-bill R S&P 500 R (IBM) R (3M) Dec-90 -3.1 17.46 11.3 7.89 -10.99 9.57 3.41 Dec-91 30.47 -23.87 14.04 5.86 24.61 -29.73 8.18 Dec-92 7.62 -56.85 8.81 3.89 3.73 -60.74 4.92 Dec-93 10.08 11.4 10.85 3.43 6.65 7.97 7.42 Dec-94 1.32 26.33 1.49 5.32 -4 21.01 -3.83 Dec-95 37.58 21.75 25.03 5.94 31.64 15.81 19.09 Dec-96 22.96 50.59 25.04 5.52 17.44 45.07 19.52 Dec-97 33.36 32.28 1.2 5.63 27.73 26.65 -4.43 Dec-98 28.58 56.67 -11.7 5.05 23.53 51.62 -16.75 Dec-99 21.04 15.71 34.43 5.08 15.96 10.63 29.35 Dec-00 -9.1 -23.83 20.79 6.11 -15.21 -29.94 14.68 1-Dec -11.89 35.28 -1.92 3.49 -15.38 31.79 -5.41 2-Dec -22.1 -44.11 4.22 2 -24.1 -46.11 2.22

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.