Monthly Investing vs Yearly Investing – Effects on Average Share Cost

I invest in a long term S&P500 momentum strategy. I have been making monthly contributions and over the short term. The average trade cost seemed to be much higher than initial cost. I wanted to see if buying in bulk 1x a year was better than investing every month over a long run of history.

The calculation to work out the average trade cost is as follows:

Total $ amount invested / Total shares invested.

If we invest at each time increment:

# Invest - Avg Share Cost
# Rolling Calculation
prices <- c(12.56,13.45,11.10,10.09,12.3,16.7)
total.to.invest <- c(10000,7000,1600,6000,3000,12000)
table <- data.frame(prices,total.to.invest)
table$total.shares <- apply(table[,c('prices','total.to.invest')], 1, function(x) { round((x[2]/x[1])) } )
table$total.shares.sum <- cumsum(table$total.shares)
table$total.to.invest.sum <- cumsum(table$total.to.invest)
table$roll.cost.avg <- apply(table[,c('total.shares.sum','total.to.invest.sum')], 1, function(x) { (x[2]/x[1]) } ) > head(table)
  prices total.to.invest total.shares total.shares.sum total.to.invest.sum roll.cost.avg
1  12.56           10000          796              796               10000      12.56281
2  13.45            7000          520             1316               17000      12.91793
3  11.10            1600          144             1460               18600      12.73973
4  10.09            6000          595             2055               24600      11.97080
5  12.30            3000          244             2299               27600      12.00522
6  16.70           12000          719             3018               39600      13.12127

This is on a rolling basis, we see that the average cost is higher than our initial entry which will be the case with an up trending series.

If we invest less at the beginning and more at the end:

# Invest less at beginning, Invest more later
prices <- c(12.56,13.45,11.10,10.09,12.3,16.7)
total.to.invest <- c(100,700,1600,6000,3000,120000)
table <- data.frame(prices,total.to.invest)
table$total.shares <- apply(table[,c('prices','total.to.invest')], 1, function(x) { round((x[2]/x[1])) } )
table$total.shares.sum <- cumsum(table$total.shares)
table$total.to.invest.sum <- cumsum(table$total.to.invest)
table$roll.cost.avg <- apply(table[,c('total.shares.sum','total.to.invest.sum')], 1, function(x) { (x[2]/x[1]) } )

  prices total.to.invest total.shares total.shares.sum total.to.invest.sum roll.cost.avg
1  12.56             100            8                8                 100      12.50000
2  13.45             700           52               60                 800      13.33333
3  11.10            1600          144              204                2400      11.76471
4  10.09            6000          595              799                8400      10.51314
5  12.30            3000          244             1043               11400      10.93001
6  16.70          120000         7186             8229              131400      15.96792

As we invested a higher amount of capital later than earlier, we brought our average cost up and closer to the current price. Its a weighted average after all.

Next we simulate adding a fixed amount of capital every month and a fixed amount of capital every year.

Two investment capitals:

# Enter monthly and yearly capital investments
capital.invest.e.month <- 10000/12 # Invest 10,000 a year, split into months
bulk.invest.1.year <- 10000

We invest 10,000 per year divided by 12 months, 833.33 each month and 10,000 every year buying the S&P500. Orders are placed at the start of every month and yearly orders at the start of every year.

We then compute a rolling average cost, the code to do this:

# Bulk Buy 1x a year Vs investing every month
# Andrew Bannerman 1.3.2018

require(xts)
require(data.table)
require(ggplot2)
require(lubridate)
require(magrittr)
require(scales)
require(reshape2)
require(PerformanceAnalytics)
require(dplyr)
require(TTR)

# Download SPX data
require(quantmod)
startdate<- "1930-01-01"
SPX <- getSymbols("^GSPC",from=startdate,auto.assign=FALSE)
SPX <-  data.frame(Date=index(SPX), coredata(SPX)) # Change XTS to data frame and retain Date column

# Add day of month column 
# This is a helper column for creating buy/sell rules
months <- SPX %>% dplyr::mutate(month = lubridate::month(Date)) %>% group_by(month) 
days <- SPX %>% dplyr::mutate(day = lubridate::day(Date)) %>% group_by(day) 
years <- SPX %>% dplyr::mutate(year = lubridate::year(Date)) %>% group_by(year) 
df <- data.frame(SPX,month=months$month,day=days$day,year=years$year)

# Subset df by date 
#df <- subset(df, Date >= as.Date("2009-01-01"))
head(df$Date)

# Enter monthly and yearly capital investments
capital.invest.e.month <- 10000/12 # Invest 10,000 a year, split into months
bulk.invest.1.year <- 10000

# Simulate buying every month
output <- df %>%
  dplyr::mutate(RunID = data.table::rleid(month)) %>%
  group_by(RunID) %>%
  mutate(ID.No = row_number()) %>%
  dplyr::mutate(total.shares.months = ifelse(ID.No == 1,first(capital.invest.e.month) / first(GSPC.Adjusted),0)) %>%  # Divide total purchased by cost price for total share
  dplyr::mutate(total.cost.months = ifelse(ID.No == 1,first(total.shares.months) * first(GSPC.Adjusted),0)) %>%  # Divide total purchased by cost price for total share
  ungroup() %>%
  select(-RunID)

df <- data.frame(output)
head(df$Date)
# Simulate buying 1x share start of each month
#output <- df %>%
#dplyr::mutate(RunID = data.table::rleid(month)) %>%
#group_by(RunID) %>%
#  mutate(ID.No = row_number()) %>%
#  dplyr::mutate(first.month.total.cost = ifelse(ID.No == 1,first(GSPC.Adjusted) * 1,0)) %>% # Own 1x share at close price change 1 to 2 for more..
#  dplyr::mutate(total.shares = ifelse(ID.No == 1,first(first.month.total.cost) / first(GSPC.Adjusted),0)) %>%  # Divide total purchased by cost price for total share
#    ungroup() %>%
#  select(-RunID)

# Simulate bulk investing 1x a year
output <- df %>%
  dplyr::mutate(RunID = data.table::rleid(year)) %>%
  group_by(RunID) %>%
  mutate(ID.No = row_number()) %>%
  dplyr::mutate(total.shares.years = ifelse(ID.No == 1,round(first(bulk.invest.1.year) / first(GSPC.Adjusted)),0)) %>%  # Divide total purchased by cost price for total share
  dplyr::mutate(total.cost.years = ifelse(ID.No == 1,first(total.shares.years) * first(GSPC.Adjusted),0)) %>%  # Divide total purchased by cost price for total share
  ungroup() %>%
  select(-RunID)
# output data frame
df <- data.frame(output)

# Calculate average cost per share 
# sum first.month.total cost / sum of total shares bought
month.invest.avg.cost <- sum(df$total.cost.months) / sum(df$total.shares.months)
year.invest.avg.cost <- sum(df$total.cost.years) / sum(df$total.shares.years)
find.first.price <- head(df$GSPC.Adjusted,1)
find.last.price <- tail(df$GSPC.Adjusted,1)

# Subset for month avg cost
# index
df$index <- seq(1:nrow(df))
df.month <- subset(df,total.shares.months >0)
# totals 
df.month$total.shares.months.sum <- cumsum(df.month$total.shares.months)
df.month$total.cost.months.sum <- cumsum(df.month$total.cost.months)
df.month$month.roll.avg.cost <- apply(df.month[,c('total.cost.months.sum','total.shares.months.sum')], 1, function(x) { (x[1]/x[2]) } )
head(df.month$Date)
# Join original df 
df.join.month <- full_join(df, df.month, by = c("Date" = "Date"))
df.join.month$month.roll.avg.cost <- na.locf(df.join.month$month.roll.avg.cost)
head(df.join.month$Date)

# Subset for year avg year cost
df.year <- subset(df,total.shares.years >0)
# totals 
df.year$year.total.shares.years.sum <- cumsum(df.year$total.shares.years)
df.year$year.total.cost.years.sum <- cumsum(df.year$total.cost.years)
df.year$year.roll.avg.cost <- apply(df.year[,c('year.total.cost.years.sum','year.total.shares.years.sum')], 1, function(x) { (x[1]/x[2]) } )

# Join original df 
df.join.year <- full_join(df, df.year, by = c("Date" = "Date"))
df.join.year$year.roll.avg.cost <- na.locf(df.join.year$year.roll.avg.cost)
tail(plot.df,1000)
# Plot 
plot.df  <- data.frame("Date" = df.join.month$Date, "Rolling Average Cost Monthly" = df.join.month$month.roll.avg.cost,"Rolling Average Cost Yeary" = df.join.year$year.roll.avg.cost, "SPX Adjusted Close" = df$GSPC.Adjusted)
# Melt for plotting
plot.df <- melt(plot.df, id.vars="Date")
ggplot(plot.df, aes(x=Date, y=value, fill=variable)) +
  geom_bar(stat='identity', position='dodge')+  
  ggtitle("Average Share Cost - Investing Monthly Vs 1x Bulk Investing Each Year",subtitle="SPX 1950 To Present")+
  labs(x="Date",y="SPX Close Price")+
  theme(plot.title = element_text(hjust=0.5),plot.subtitle =element_text(hjust=0.5))

And the output vs SPX price:

Rplot238
Average Share Cost – Invest start of every month and start of every year – 1950 To Present

From 1950 to present we see we lock in a better average cost pre dot com up trend.

Rplot234
Average Share Cost – Invest start of every month and start of every year – 2000 To Present

If we started investing at the top of the dot com bubble. We see the effects of averaging down. Both bear markets are somewhat averaged out so to speak. Investing at the top of the market, buy and hold gains were somewhat stagnant for an entire decade.

Rplot235
Average Share Cost – Invest start of every month and start of every year – 2009 To Present

There is not much variation between electing to invest every month vs every year. The difference is negligible over the long haul. It is however, notable that investing incrementally affects the total % return. If we bulk invest 10,000 at one price and it rises 10%. We have +$1000. If we dollar cost average, we invest 20,000 and dilute the initial cost, we have a 5% gain instead of a 10% gain. However, 5% gain on 20,000 is 1000.

Raising the cost per share as price trends does affect the amount of cushion one has. As in this example, this is conditionally investing in the momentum strategy:

Rplot252

This is bulk investing $50,000 at a new leg and adding a fixed amount every month / year. We see our average cost chases the current price. One way one could avoid is bulk invest at the start of each new leg without adding any new funds. Only large contributions are made when a new momentum trade is entered, which is in this case is every other year for my S&P500 strategy. More to come.

Thanks for reading!

Full code can be found on my github.

Author: Andrew Bannerman

Integrity Inspector. Quantitative Analysis is a favorite past time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s