Setting Initial Value for Stock and Rolling Multiple with dplyr::mutate

Question

Beginning with this dataset:

df <- data.frame(
  year = 2007,
  month = c('Jan', 'Feb', 'Mar', 'April'),
  percent_change = c(0.0314, 0.0073, 0.0135, -0.0144),
  to_multiply = c(1.0314, 1.0073, 1.0135, .9856)
)

I would like to produce the following dataset.

year month percent_change to_multiply dollar_value
2007   Jan         0.0314      1.0314     103.1400
2007   Feb         0.0073      1.0073     103.8929
2007   Mar         0.0135      1.0135     105.2955
2007 April        -0.0144      0.9856     103.7792

I am wondering how to programmatically assign an initial value and create a new column displaying the value of a stock after each month. This is easy to do in a spreadsheet, but I'd like to stay away from that so I can rapidly chart different start dates and/or allocations using the same initial start value.

I have tried mutate with a lag wrapper, but I couldn't get that to work. I looked at RCCPRoll, but also couldn't make that work.

Please include the images and a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example); we can't help you with what you've put here. — RobertMyles, Jun 14 '17 at 20:53
I have deleted to image files and replaced them with reproducible examples. I didn't include code attempts because I'm stuck on how to approach this. I apologize for not following S/O question protocol. — AaronT86, Jun 14 '17 at 21:22
In regard to your fundamental question about how to "link values" like in a spreadsheet; `cumsum`, `cumprod`, and `diff` handle most situations, but when you need more it gets tricky. `zoo::rollapply` can be helpful, but generally you need to initialize a column and then do a loop. — geneorama, Jun 15 '17 at 14:10

score 1 · Accepted Answer · answered Jun 14 '17 at 21:29

The thing that would help you is cumprod, but can I suggest using data.table?

dt <- data.table(year = 2007,
                 month = c('Jan', 'Feb', 'Mar', 'April'),
                 percent_change = c(0.0314, 0.0073, 0.0135, -0.0144),
                 to_multiply = c(1.0314, 1.0073, 1.0135, .9856))
dt[ , newvalue := cumprod(to_multiply) * 100]
dt
#    year month percent_change to_multiply newvalue
# 1: 2007   Jan         0.0314      1.0314 103.1400
# 2: 2007   Feb         0.0073      1.0073 103.8929
# 3: 2007   Mar         0.0135      1.0135 105.2955
# 4: 2007 April        -0.0144      0.9856 103.7792

Actually, a shorter version:

dt <- data.table(year = 2007,
                 month = c('Jan', 'Feb', 'Mar', 'April'),
                 percent_change = c(0.0314, 0.0073, 0.0135, -0.0144))
dt[ , newvalue := 100 * cumprod(1 + percent_change)]
dt
#    year month percent_change newvalue
# 1: 2007   Jan         0.0314 103.1400
# 2: 2007   Feb         0.0073 103.8929
# 3: 2007   Mar         0.0135 105.2955
# 4: 2007 April        -0.0144 103.7792

Setting Initial Value for Stock and Rolling Multiple with dplyr::mutate

1 Answers1