How to generate a sequence of numbers increasing at a fixed percentage?

user_8673515 0
Viewed 88

I would like to calculate the predicted value at 2% growth rate over 10 years.

My data looks like this

df <- structure(list(fin_year = c(2016, 2017, 2018, 2019, 2020, 2021
 ), Total = c(136661.9, 142748.25, 146580.77, 155486.07, 171115.58, 
              69265.01)), class = "data.frame", row.names = c(NA, -6L))

I would like to add a new column (two_percent) with the calculated amounts based on the 2016 Total value.

I expect the answers to look like this:

enter image description here

I've tried this but can't figure out how to code the script properly to do what I want

df1 <- df %>% 
      mutate(two_percent = rep(Total[1:1] *1.02))

Your help is much appreciated

dplyr r
3 Answers

The formula is 1.02^n where n is the number of periods. One may need to subtract 1 from n depending on whether the interest is at the beginning or end of the period.

basevalue <- df$Total[1]
df1 <- df %>% 
   mutate(two_percent = basevalue*1.02^(row_number()-1))
user_57922440

We can use purrr::accumulate to calculate the 2% growth forecast. First let's calculate this for the existing data.frame. We need to supply a vector of 1.02 in the length of one less than the total row number to accumulates .x argument. Further, we need the base value of Total as .init argument (this is the value we want to base the forecast on). The function .f that we then use is just .x * .y.

library(dplyr)
library(purrr)

# Calculate the growth rate for the existing data.frame
df %>% 
  mutate(two_percent = accumulate(rep(1.02, nrow(.)-1),
                                  ~ .x * .y,
                                  .init = first(Total)))
#>   fin_year     Total two_percent
#> 1     2016 136661.90    136661.9
#> 2     2017 142748.25    139395.1
#> 3     2018 146580.77    142183.0
#> 4     2019 155486.07    145026.7
#> 5     2020 171115.58    147927.2
#> 6     2021  69265.01    150885.8

While this works for the existing data.frame we need a new one, if we want to forecast values for years that the current df doesn't contain. Basically, we use the same approach as above and combine it with a right_join:

# Calculate the growth rate for a 10 year period, and then join
new_df <- tibble(Year = 1:10,
                 two_percent = df$Total[1]) %>% 
  mutate(two_percent = accumulate(rep(1.02, nrow(.)-1),
                                  ~ .x * .y,
                                  .init = first(two_percent)))
df %>% 
  mutate(Year = row_number()) %>% 
  right_join(new_df)
#> Joining, by = "Year"
#>    fin_year     Total Year two_percent
#> 1      2016 136661.90    1    136661.9
#> 2      2017 142748.25    2    139395.1
#> 3      2018 146580.77    3    142183.0
#> 4      2019 155486.07    4    145026.7
#> 5      2020 171115.58    5    147927.2
#> 6      2021  69265.01    6    150885.8
#> 7        NA        NA    7    153903.5
#> 8        NA        NA    8    156981.6
#> 9        NA        NA    9    160121.2
#> 10       NA        NA   10    163323.6

Created on 2022-01-13 by the reprex package (v2.0.1)

user_93493020

Here's another simple method that anchors the base of the Two_percent calculation to the value of Total in the first fin_year using which.min(fin_year)

library(tidyverse)

df <- structure(list(fin_year = c(2016, 2017, 2018, 2019, 2020, 2021
), Total = c(136661.9, 142748.25, 146580.77, 155486.07, 171115.58, 
             69265.01)), class = "data.frame", row.names = c(NA, -6L))

df %>% 
  mutate(two_percent = Total[which.min(fin_year)] * 1.02^(seq_along(fin_year)))
#>   fin_year     Total two_percent
#> 1     2016 136661.90    139395.1
#> 2     2017 142748.25    142183.0
#> 3     2018 146580.77    145026.7
#> 4     2019 155486.07    147927.2
#> 5     2020 171115.58    150885.8
#> 6     2021  69265.01    153903.5

Created on 2022-01-13 by the reprex package (v2.0.1)

user_132105540
Related