How to create a variable based on a given growth rate using xts?

Question

I want to create a variable based on a given growth rate, which is in another variable, starting from a base of 1. Below is an example. I tried two methods, either applying growth rate to lagged value or compound formula for the case of constant growth rate). None of the method works.

Note: For simplicity, growth rate is constant at 10%, but that is not the case of my data. (Could use for instance growth rate of variable in data, with diff(data)/lag(data))

Update: added target result as variable

library(xts)
data <- as.xts(AirPassengers)
gr <- 0.1 # 10% growth
data <- cbind(data,gr)
# Method 1, based on lag
data$v2 <- 1
data$v2 <- lag(data$v2)*(1+data$gr)
# Method 2, based on compound growth formula
data$v3 <- 1
data$v3 <- first(data$v3)*(1+data$gr)^(index(data$gr))
data$target <- 1*1.1^(1:144) # vector growing 10%, starting at 1 

What is it that I'm doing wrong? Can't work it out from vignette or other help online.

Answer 1

Here are two ways.
The first uses a for loop in order to make the calculations being performed more clear. The main problem is to coerce the data in data to numeric values.

suppressPackageStartupMessages({
  library(xts)
  library(tidyverse)
})

data <- as.xts(AirPassengers)
gr <- 0.1 # 10% growth
data <- cbind(data, gr)

n <- nrow(data)
data$v2 <- NA_real_
for(i in seq.int(n)[-1L]) {
  data_prev <- data$data[i - 1L] |> unclass() |> c()
  r <- (1 + data$gr[i]) |> unclass() |> c()
  data$v2[i] <- data_prev * r
}
data %>% head()
#>          data  gr    v2
#> jan 1949  112 0.1    NA
#> fev 1949  118 0.1 123.2
#> mar 1949  132 0.1 129.8
#> abr 1949  129 0.1 145.2
#> mai 1949  121 0.1 141.9
#> jun 1949  135 0.1 133.1

^{Created on 2023-11-28 with reprex v2.0.2}

---

The second way uses a dplyr pipe to produce the same vector v2. And then computes a vector v3 from the growth rates given by the data column's variations.

suppressPackageStartupMessages({
  library(xts)
  library(tidyverse)
})

data <- as.xts(AirPassengers)
gr <- 0.1 # 10% growth
data <- cbind(data, gr)

new_data <- data %>%
  as.data.frame() %>%
  mutate(gr3 = (data - dplyr::lag(data, default = NA_real_))/data) %>%
  mutate(
    v2 = dplyr::lag(data, default = NA_real_) * (1 + gr),
    v3 = dplyr::lag(data, default = NA_real_) * (1 + gr3)
  )

new_data %>% head()
#>          data  gr         gr3    v2       v3
#> jan 1949  112 0.1          NA    NA       NA
#> fev 1949  118 0.1  0.05084746 123.2 117.6949
#> mar 1949  132 0.1  0.10606061 129.8 130.5152
#> abr 1949  129 0.1 -0.02325581 145.2 128.9302
#> mai 1949  121 0.1 -0.06611570 141.9 120.4711
#> jun 1949  135 0.1  0.10370370 133.1 133.5481

^{Created on 2023-11-28 with reprex v2.0.2}

---

Edit

What follows is not the best example to illustrate my comment but it is based on what seems to be one of the question's problems.

n <- nrow(data)
data$v2 <- 1

# for loop, updates v2 on each iteration
for(i in seq.int(n)[-1L]) {
  v2_prev <- data$v2[i - 1L] |> unclass() |> c()
  r <- (1 + data$gr[i]) |> unclass() |> c()
  data$v2[i] <- v2_prev * r
}
data$v2 %>% head()
#>               v2
#> jan 1949 1.00000
#> fev 1949 1.10000
#> mar 1949 1.21000
#> abr 1949 1.33100
#> mai 1949 1.46410
#> jun 1949 1.61051

# vectorized calculation, identical results
(1 + 0.1)^(seq_len(n) - 1L) |> head()
#> [1] 1.00000 1.10000 1.21000 1.33100 1.46410 1.61051

^{Created on 2023-11-29 with reprex v2.0.2}

Answer 2

You can do this without any packages using cumprod().

gr <- rep(0.1, 5)
x <- cumprod(1 + gr)
x
## [1] 1.10000 1.21000 1.33100 1.46410 1.61051

And cumprod() works with xts objects.

gr_xts <- xts(gr, .Date(seq_along(gr)))
cumprod(1 + gr_xts)
##               [,1]
## 1970-01-02 1.10000
## 1970-01-03 1.21000
## 1970-01-04 1.33100
## 1970-01-05 1.46410
## 1970-01-06 1.61051