i. EXECUTIVE SUMMARY
ii. ANALYSIS
iii. TABLES & FIGURES
iv. CODE

EXECUTIVE SUMMARY

In this assignment, I calculate the annual compounded rates of return for each stock in the S&P500 from January 1926 through December 2019. I do this by calculating the rate of compounded growth in each stock’s market cap from year to year; specifically, I do not calculate the rate of return on the stock price because I know that there have been stock splits during the 1926-2019 time period, which arbitrarily shifts the stock price during stock split years. For each stock, I save the initial market cap on the first year of the stock’s existence in my dataset. Then for each subsequent year, I calculate the compounded rate of return for the stock (relative to its initial market cap) via the formula \[Compounded \space Return = (\frac{Current \space Market \space Cap}{Initial \space Market \space Cap})^{\frac{1}{years \space passed \space since \space initial \space year}} -1\].

In the end, once I have the compounded rates of return for each stock during the life of the stock in my dataset, I take the average, median, maximum, and minimum compounded rate of return across all stocks to see how stocks in general performed in terms of compounded rate of return during the time period 1926-2019.

I find that the average annual compounded rate of return was 10.42% and the median annual compounded rate of return was 9.47% across all stocks for the time period 1926 to 2019. Also, the highest annual compounded rate of return was 1389.11%, which took place for UNION ELECTRIC CO during its first year in my dataset, from 1953 to 1954. The lowest annual compounded rate of return was -89.29%, which took place for CAMERON BROWN INVT GROUP, also during its first year in my dataset, from 1973 to 1974.

ANALYSIS

I retrieved the data for the (CRSP) S&P 500 monthly stock prices, shares outstanding, and corresponding dates for individual constituent stocks in the S&P 500 from the WRDS database. After I retrieved the ticker, prices, shares outstanding, and corresponding dates for each individual stock, I import the data into R and house it in a dataframe so I can access it. I calculate the market cap for each stock on each day by multiplying the stock price and the shares outstadning together. Once I have the market cap for each stock, I calculate the annual compounded rate of return for each stock by initializing its begininng market cap and looping through my dataframe from year to year by using the following formula: \[Compounded \space Return = (\frac{Current \space Market \space Cap}{Initial \space Market \space Cap})^{\frac{1}{years \space passed \space since \space initial \space year}} -1\].

I then take these compounded annual rates of returns and create summary statistics, such as the mean, median, maximum, and minimum return. I also plot a histogram of annual compounded rates of returns, whereby I cap off the x-axis of the graph to only show returns between -100% and 100% so that the data can be visualized better in the histogram. From the histogram, we can see that the annual compounded returns were mostly between -25% to 50%, with a majority of returns straddling 10%.

TABLES & FIGURES

Annual Compounded Rates of Return for S&P500 stocks (in %)
Annual Compounded Rate of Return Company Ticker PERMNO CUSIP Year Market Cap
Mean 10.4190770795511 NA NA NA NA 1926-2019 NA
Median 9.47399918585479 NA NA NA NA 1926-2019 NA
Max 1389.11363394094 UNION ELECTRIC CO UEP 24985 02360810 1953-1954 279387500
Min -89.2857142857143 CAMERON BROWN INVT GROUP CB 56469 86787210 1973-1974 5307750

CODE

library(dplyr)
library(lubridate)

sp <- read.table('stock_data.csv',sep=',',header=TRUE)  #using data from 1970-2019
sp <- data.frame(sp)
str(sp)  #date object is char...
## 'data.frame':    330813 obs. of  7 variables:
##  $ PERMNO: int  10057 10057 10057 10057 10057 10057 10057 10057 10057 10057 ...
##  $ date  : chr  "01/30/1926" "02/27/1926" "03/31/1926" "04/30/1926" ...
##  $ TICKER: chr  "" "" "" "" ...
##  $ COMNAM: chr  "NATIONAL ACME CO" "NATIONAL ACME CO" "NATIONAL ACME CO" "NATIONAL ACME CO" ...
##  $ CUSIP : chr  "00462610" "00462610" "00462610" "00462610" ...
##  $ PRC   : num  -11.81 -10.25 9.25 -9.25 -8.5 ...
##  $ SHROUT: int  500 500 500 500 500 500 500 500 500 500 ...
sp = na.omit(sp)  #remove the NA's
for(i in 1:nrow(sp)){
  if(sp$PRC[i] < 0){
    sp$PRC[i] = -sp$PRC[i]
  }
}

sp <- sp %>%
  mutate(date = as.Date(date, format = "%m/%d/%Y"),
         Market_Cap = PRC*SHROUT*1000,
         cum_return = NA) 

perm = sp$PERMNO[1]  #initialize with the first stock's permno
initial_price = sp$Market_Cap[1]  #initialize with the first stock's price (which I use to get cum_returns)
year_count = 0 #initialize year count to 0... this will be used to get cum_returns

rows_with_newyear = seq(from=13, to=259362, by= 12)  #the rows in sp that are one year apart
for(i in rows_with_newyear){   #2nd year is at 13, 3rd year is at 25
  if(sp$PERMNO[i] == perm){
    year_count = year_count +1
    sp$cum_return[i] = ((sp$Market_Cap[i]/initial_price)^(1/year_count)) -1 #formula with PRC at row i vs initial_price and using year_count  
  }
  else{
    perm = sp$PERMNO[i]  #initialize with the first stock's permno
    initial_price = sp$Market_Cap[i]
    year_count =0
  }
}



sp_a = na.omit(sp)
#hist(sp_a$cum_return*100, breaks = 1000, xlim = c(-100,100), col = 'blue', main = "Annual Compounded Rates of Return for S&P500 stocks", xlab = "Compounded Rate of Return (in %)")
minimum_compounded_return = min(sp_a$cum_return)*100
maximum_compounded_return = max(sp_a$cum_return)*100
average_compounded_return = mean(sp_a$cum_return)*100
median_compounded_return = median(sp_a$cum_return)*100


#finding which stock had the max/min performance and in which year? (compounded since holding from inception to year x)
for (i in 1:nrow(sp_a)) {
  if(sp_a$cum_return[i] == max(sp_a$cum_return)){
    max_return_stock = sp_a$TICKER[i]
    max_return_company = sp_a$COMNAM[i]
    max_return_date = sp_a$date[i]
    max_return_row = i
    max_return_PERMNO = sp_a$PERMNO[i]
    max_return_CUSIP = sp_a$CUSIP[i]
    max_return_MarketCap = sp_a$Market_Cap[i]
  }
  if(sp_a$cum_return[i] == min(sp_a$cum_return)){
    min_return_stock = sp_a$TICKER[i]
    min_return_company = sp_a$COMNAM[i]
    min_return_date = sp_a$date[i]
    min_return_row = i
    min_return_PERMNO = sp_a$PERMNO[i]
    min_return_CUSIP = sp_a$CUSIP[i]
    min_return_MarketCap = sp_a$Market_Cap[i]
    
  }
}

REFERENCES

Wharton Research Data Services. “CRSP Monthly Stock” wrds.wharton.upenn.edu, accessed 01/30/2021.