Starting period and ending period

Question

I have created this data table in R and now I would like to compute the cumulative return of a stock (stock is shown by ID and a stock can be seen on different time periods). If I have month t, the cumulative return should be computed from month t-11 to month t - 1. Columns new_11 and new_1 are helping columns which show me for eg on first row, for Dec 1980 cumulative return should be computed from Jan 1980 to Nov 1980, grouped by Id. I know the cumulative return formula in R is cumprod(1 + RETURN), but I do not know how to write the code according to starting and ending month.

Can anyone please help me? Press to see the table -> 1

It is much more difficult to help without a reproducible example, see here: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Axeman, Jun 07 '22 at 23:36
Please provide enough code so others can better understand or reproduce the problem. — Community, Jun 08 '22 at 01:06

Carl · Accepted Answer · 2022-06-08T11:01:07.093

Try this using slider to evaluate the prior sliding 11 months:

library(tidyverse)
library(lubridate)
library(tsibble)
library(slider)

tribble(
  ~id, ~yr_mon, ~return,
  1, "Nov 1979", 0.10,
  1, "Dec 1979", 0.11,
  1, "Jan 1980", 0.12,
  1, "Feb 1980", 0.13,
  1, "Mar 1980", 0.14,
  1, "Apr 1980", 0.15,
  1, "May 1980", 0.16,
  1, "Jun 1980", 0.17,
  1, "Jul 1980", 0.18,
  1, "Aug 1980", 0.19,
  1, "Sep 1980", 0.20,
  1, "Oct 1980", 0.21,
  1, "Nov 1980", 0.22,
  1, "Dec 1980", 0.23
) |> 
  mutate(yr_mon = parse_date(yr_mon, format = c("%b %Y")) |> yearmonth()) |> 
  arrange(id, yr_mon) |> 
  group_by(id) |> 
  mutate(cum_ret = slide_dbl(lag(return), ~ last(cumprod(1 + .x)), .before = 10, .complete = TRUE))

#> # A tibble: 14 × 4
#> # Groups:   id [1]
#>       id   yr_mon return cum_ret
#>    <dbl>    <mth>  <dbl>   <dbl>
#>  1     1 1979 Nov   0.1    NA   
#>  2     1 1979 Dec   0.11   NA   
#>  3     1 1980 Jan   0.12   NA   
#>  4     1 1980 Feb   0.13   NA   
#>  5     1 1980 Mar   0.14   NA   
#>  6     1 1980 Apr   0.15   NA   
#>  7     1 1980 May   0.16   NA   
#>  8     1 1980 Jun   0.17   NA   
#>  9     1 1980 Jul   0.18   NA   
#> 10     1 1980 Aug   0.19   NA   
#> 11     1 1980 Sep   0.2    NA   
#> 12     1 1980 Oct   0.21    4.63
#> 13     1 1980 Nov   0.22    5.10
#> 14     1 1980 Dec   0.23    5.60

# Check for Oct 1980 (Nov to Sep)
cumprod(1 + seq(0.1, 0.2, 0.01)) |> last()
#> [1] 4.63307

^{Created on 2022-06-08 by the reprex package (v2.0.1)}

the class is already by date, the issue now is that I don't need the cum_ret over the 12 month period. If we take for eg stock 1 & yr_mon Oct 1980, I need the cum_ret from starting period November 1979 until September 1980 (overall 11 months, 1 month must be omitted). I hope this is somehow understandable, I am sorry I am very new at this PS: that is why on my table i have included the helping columns that show me which is the starting period and the ending period that i need to calculate the cum_ret — Antisa Qipro, Jun 08 '22 at 10:16
I think it's working. Thank you so much! You just saved a student's life haha — Antisa Qipro, Jun 08 '22 at 13:03

Starting period and ending period

1 Answers1