Calculating Lagged Twelve-Month Returns Across R Data Columns

Summary

The document describes a data-processing problem involving a wide monthly return table with more than a thousand company columns and an eleven-year sample. The goal is to calculate a rolling cumulative return over the previous twelve months, with a one-month lag: for each output month, the return window ends in the month before it. The example specifies that a January 2005 estimate uses January through December 2004, while February uses February 2004 through January 2005.

The input includes missing values, which the author says must remain because they reflect the source data. The document asks how to perform this calculation in R but includes no proposed function, code, output, or treatment rule for missing observations. That omission matters because the resulting cumulative return can depend on whether a window containing missing values is left missing, partially aggregated, or handled another way. The material is therefore a practical rolling-window question, with the lag and NA constraint clearly stated but the computation and missing-data policy unresolved.

Key ideas

• The requested statistic is a cumulative return over the previous twelve monthly observations.
• The window ends one month before the month associated with each calculated value.
• The data are arranged with companies in columns and monthly estimated returns in rows.
• Missing values are part of the data and cannot simply be removed.
• The document asks for an R approach but does not specify a missing-value calculation policy or provide a solution.

Tags

# How to calculate cumulative returns with one lag in R


# How to calculate cumulative returns with one lag in R












I have a huge data frame with over 1000 column, which are companies(column headers) and in each column I have their estimated return(monthly). The sample period of the data frame in 11 years. I want to calculate cumulative returns over the past 12 months with with one month lag. Meaning cumulative returns of January 2005 are estimated on the basis of Jan-04 to Dec.04 and February 2005 are estimated on the basis of Feb-04 to Jan-05. I present my data as follows:

```
df
Month    A       B      C     D      E
Jan-00  0.01    0.00    NA  -0.01   NA
Feb-00  0.01    0.00    NA  0.00    NA
Mar-00  -0.02   0.00    NA  0.01    NA
Apr-00  -0.01   0.00    NA  -0.01   NA
May-00  -0.01   0.00    NA  0.01    NA
Jun-00  0.00    0.01    NA  -0.01   NA
Jul-00  0.00    -0.01   NA  0.00    NA
Aug-00  0.00    0.01    NA  0.00    NA
Sep-00  0.00    0.00    NA  0.00    NA
Oct-00  -0.01   0.00    NA  0.00    NA
Nov-00  -0.01   -0.01   NA  0.01    NA
Dec-00  -0.01   0.00    NA  0.01    NA
Jan-01  -0.01   0.00    NA  0.00    NA
```

There also NAs within the data which cannot be removed due to the nature of data. I appreciate your help in this regard.

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.