Please do this assignment by yourself. If you get stuck, it’s fine to seek help from your classmates or the teaching team. Don’t hand in the data, just responses to the questions. As with the first assignment, this will be graded largely on effort. The assignment’s objective is threefold: to answer specific questions; to gain experience working with data—e.g., computing returns, lagging variables, seeing the necessity of multiple regressions; and developing an empirical frame of mind. Does gold have a negative beta? Is the January effect for real? Do multinational firms give an investor international exposure? Do a small number of stocks produce meaningful diversification? There’s nothing like checking this with data on your own.
International Business Machines (symbol: IBM)
Merck (symbol: MRK)
Exxon Mobil (symbol: XOM)
McDonalds (symbol: MCD)
S&P 500 Index
Nikkei 225
FTSE 100
We will work with monthly data from January 1985 to October 2022. The convention for using monthly financial data collects end-of-day prices from the last business day of each month.
In the past in a similar assignment, I asked students to gather the data directly from Yahoo Finance. But this turns out to be time-consuming unless you’re accustomed to it. The spreadsheet posted on Courseworks shows the month-end closing prices for the stocks and stock indices.
We want to compute the percent change every month. To save time, we’re ignoring dividends; incorporating dividends won’t change the results by much. But stock splits cannot be ignored. A 3:1 stock split (pronounced “3 for 1”) means that holders of 1 share now receive 3 shares. To keep the company’s value about the same, the stock price falls by about a factor of 3. IBM split 2:1 on May 28, 1997 and 2:1 on May 27, 1999. Merck’s splits were 2:1 on May 27, 1986, 3:1 on May 26, 1988, 3:1 on May 26, 1992, and 2:1 on February 17, 1999. Exxon Mobil’s splits were 2:1 on September 15, 1987, 2:1 on April 14, 1997 and 2:1 on July 19, 2001. This data is already adjusted for you—you don’t have to worry about it. For example, at the end of June 2001, Exxon’s shares were 87.35 and at the end of July 2001 at 41.76. Since the stock split 2:1, the return was 41.76/(87.35/2) − 1 = −4.38%. The data set divides the June price by 2.
Another nuance arises from spin-offs. In May 2021, Merck spun off the women’s health care company, Organon. For every 10 shares of Merck a shareholder owned on the record date, they received one share of the new company Organon. Everything else equal, the share price of Merck dropped that day, even though holders of Merck did not lose money.If we wanted to be careful, we’d add about $3.70 to the June returns. Similarly, IBM spun off Kyndryl in November 2021. We won’t bother adjusting for Kyndryl, either; the effect on IBM returns that month was less than 2%.
When you are finished, you will have 454 data points on the returns (from 455 prices). This will consist of every month from January 1985 to October 2022.
Find returns over the same period for the FTSE 100 (UK stock index pronounced “footsie”), Nikkei 225 (Japanese stock index pronounced “nee-kay”) and S&P 500 (U.S.). Again, we ignore dividends.
Please compute the annualized returns and standard deviation for each of the six variables over the entire time horizon. What is the correlation between the current month’s return in the S&P 500 and the return in the previous month? Do you find any evidence for “trends”? Is the result statistically significant? A simple way to find out is to perform a regression (Y = current month’s return, X = last month’s return) and look at the t-statistic. (If you are using Excel, you may need to install the Analysis ToolPak.)
The annualized return is the monthly return multiplied by 12. (Note: This is a summary statistic that is simple to interpret. It is a consistent, unbiased estimator of the mean monthly return. Don’t worry about trying to compound it.) The annualized standard deviation is the standard deviation of the monthly returns multiplied by the square root of twelve. (i.e., @stdev(….) * sqrt(12)) We will motivate this calculation, briefly, later in the semester when we discuss hedge funds.
Some people believe that a multinational company can substitute for exposure to foreign stock markets. Let’s see if it is true. Consider IBM, for example. IBM derives 45% of its revenues from outside the U.S. To see whether IBM is driven by U.S. or a combination of U.S. and foreign markets, regress IBM’s return on the returns to the three stock indices. What do you find? You’d find similar results with the other three stocks, MCD, MRK and XOM. If you feel like it, you can check.
What is IBM’s beta? (Hint: Regress IBM’s return on the S&P 500). How confident are you that your estimate is accurate? (Hint: The regression output should report the standard error or confidence interval).
Investors can achieve a surprising amount of diversification from a small number of assets. Consider a portfolio that is equally weighted in MRK, XOM, MCD and IBM. It is rebalanced monthly, so the portfolio returns equal the unweighted average of the returns of the three individual stocks. (i.e., compute the monthly return of all three stocks, add them up and divide by three.) What is the mean and volatility of the returns to this portfolio? How does it compare to the S&P 500?
Cut the data set in half. During the most recent 50%, find the correlations between S&P and Nikkei; S&P and FTSE; Nikkei and FTSE. Do the same thing for the half of your data beginning with January 1985 returns. Are correlations rising? What would you conclude?
According to Wikipedia, “Historically if the S&P 500 goes up in January the trend will follow for the rest of the year. Conversely if the S&P falls in January then it will fall for the rest of the year.” Is this true? In one column, put the return for January in each year from 1985 to 2021 and in the next column the sum of the monthly returns for the following eleven months. (Ignore 2022 because we don’t yet have the 11-month return.) Regress the 11-month return (dependent variable = Y) on the January return. Is the coefficient significantly different from zero? The data you need is on the second tab in the spreadsheet provided.
According to some investing websites, gold has a negative beta. Does it? You can find spot gold prices in the spreadsheet.
How much do returns on ExxonMobil stock depend on those to crude oil? First, regress XOM returns on current percentage changes in crude oil. Does XOM respond to crude oil with a lag? The stock market as a whole may be correlated to crude oil. Perhaps the relationship between XOM and crude is a spurious correlation arising because the S&P is correlated to crude and XOM is correlated to the S&P returns. To look a bit more deeply, regress XOM simultaneously on the percentage change in crude and the S&P returns. Finally, does crude predict XOM? Try regressing XOM on the lagged percentage change in crude. You can also find spot oil prices in the spreadsheet.
