I have a time series dataset, and I'd like to get rolling 12-month cumulative returns. Below is what my code and data look like:
df <- data.frame(
A = c(-0.0195, 0.0079, 0.0034, 0.0394, -0.0065, 0.0034, 0.0136, 0.0683, -0.0063, -0.0537, -0.0216, -0.0036, 0.0659, -0.0377, -0.0568, 0.0039, -0.0191, 0.0028),
B = c(-0.0211, 0.0021, 0.0014, 0.0358, 0.0009, 0.0153, 0.0071, 0.0658, 0.0033, -0.0542, -0.0261, 0.0064, 0.0665, -0.0304, -0.0507, 0.0089, NA, NA),
C= c(-0.0176, 0.0144, 0.0057, 0.0442, -0.0152, -0.0105, 0.0213, 0.0712, -0.0176, -0.0531, -0.0163, -0.0154, 0.0652, NA, NA, NA, NA, NA)
)
row.names(df) <- c("2016-10-31", "2016-09-30", "2016-08-31", "2016-07-31", "2016-06-30", "2016-05-31", "2016-04-30", "2016-03-31", "2016-02-29", "2016-01-31", "2015-12-31", "2015-11-30", "2015-10-31", "2015-09-30", "2015-08-31", "2015-07-31", "2015-06-30", "2015-05-31")
> df
A B C
2016-10-31 -0.0195 -0.0211 -0.0176
2016-09-30 0.0079 0.0021 0.0144
2016-08-31 0.0034 0.0014 0.0057
2016-07-31 0.0394 0.0358 0.0442
2016-06-30 -0.0065 0.0009 -0.0152
2016-05-31 0.0034 0.0153 -0.0105
2016-04-30 0.0136 0.0071 0.0213
2016-03-31 0.0683 0.0658 0.0712
2016-02-29 -0.0063 0.0033 -0.0176
2016-01-31 -0.0537 -0.0542 -0.0531
2015-12-31 -0.0216 -0.0261 -0.0163
2015-11-30 -0.0036 0.0064 -0.0154
2015-10-31 0.0659 0.0665 0.0652
2015-09-30 -0.0377 -0.0304 NA
2015-08-31 -0.0568 -0.0507 NA
2015-07-31 0.0039 0.0089 NA
2015-06-30 -0.0191 NA NA
2015-05-31 0.0028 NA NA
I want to get 12-month cumulative returns for each month using the formula prod(1+R)-1(product of 12 individual period returns minus 1). The results should be:
A(1-Y) B(1-Y) C(1-Y)
2016-10-31 0.0198 0.0322 0.0052
2016-09-30 0.1086 0.1246 0.0898
2016-08-31 0.0585 0.0881
2016-07-31 -0.0050 0.0316
2016-06-30 -0.0390 0.0048
2016-05-31 -0.0512
2016-04-30 -0.0517
B(1-Y) only has 5 cumulative returns because B has no data prior to 2015-07-31. Thus, it does not satisfy the condition of 12 months (there are only 11 months between 2015-07-31 and 2016-05-31).
I have tried Return.cumulative(df), but this function gives cumulative returns since inception, which is not what I am looking for. Any suggestion will be appreciated!
As A.Webb mentions, rollapply gives the desired result. You can also use a for loop.
a <- b <- c <- 0
for(i in (nrow(df)-11):1){
a[i] <- prod(1 + df$A[i:(i+11)]) - 1
b[i] <- prod(1 + df$B[i:(i+11)]) - 1
c[i] <- prod(1 + df$C[i:(i+11)]) - 1
}
cum.returns <- data.frame(a,b,c)