How to generate a sequence of numbers increasing at a fixed percentage?
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:
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
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))
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 accumulate
s .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)