Calculate cumulative Growth Rate based on different company categories

[Carol16]

Hi, I have problems finding out how to calculate cumulative stock return based on different company categories. Suppose there are dataframes such that:

Company=(1,1,1,1,2,2,2,3,3)
Year=(1,2,3,4,1,2,3,1,2)
Yearly_Growth=(0.1,0.2,0.3,0.1,0.1,0.05,0.1,0.1,0.1)

I would like to calculate the cumulative growth rate for different companies in certain year t from year1 {for instance, the cumulative growth rate for company 2 in year 3 should be Cum_GR=((1+1.1)(1+0.05)(1+1.1)-1).} Could somebody help me out? Thank you so much!!!

[pieterjanvc]

Hi,

I cant make much sense of your growth rate formula. Is it the product of all previous (Yearly_Growth + 1) and then subtract 1 from the result? Because I don't see where you got the 1+1.1 in your example, unless it was supposed to be 1 + 0.1.

PJ

[Carol16]

Yes! Sorry for the unclear expressions, that's exactly what I mean!

[pieterjanvc]

Hi,

What do you think of this solution:

library(dplyr)
library(purrr)

myData = data.frame(
  Company=c(1,1,1,1,2,2,2,3,3),
  Year=c(1,2,3,4,1,2,3,1,2),
  Yearly_Growth=c(0.1,0.2,0.3,0.1,0.1,0.05,0.1,0.1,0.1)
)

CGR = function(prev, new){
  (prev+1)*(new+1) - 1
}

myData = myData %>% arrange(Company, Year) %>% group_by(Company) %>% 
  mutate(cGR = accumulate(Yearly_Growth, CGR))

myData
# A tibble: 9 x 4
# Groups:   Company [3]
  Company  Year Yearly_Growth   cGR
    <dbl> <dbl>         <dbl> <dbl>
1       1     1          0.1  0.1  
2       1     2          0.2  0.32 
3       1     3          0.3  0.716
4       1     4          0.1  0.888
5       2     1          0.1  0.1  
6       2     2          0.05 0.155
7       2     3          0.1  0.271
8       3     1          0.1  0.1  
9       3     2          0.1  0.21

It took me a while to find the function I wanted, but the accumulate function from the purrr package seemed to do the trick. I wrote a special cumulative function CGR that will be called for every year per company and take the previous years into account when calculating the growth rate.

Of note, it is important that the data is sorted properly (hence using arrange) because the accumulation of data needs to be in order of year.

Hope this helps,
PJ

[pieterjanvc]

Hi,

Writing my answer, I realized there is another way of doing this that might be a bit simpler to interpret and does not use the accumulate function:

library(dplyr)

myData = data.frame(
  Company=c(1,1,1,1,2,2,2,3,3),
  Year=c(1,2,3,4,1,2,3,1,2),
  Yearly_Growth=c(0.1,0.2,0.3,0.1,0.1,0.05,0.1,0.1,0.1)
)

CGR = function(x){
  sapply(1:length(x), function(y){
    prod(1+x[1:y]) - 1
  })
}

myData %>% arrange(Company, Year) %>% group_by(Company) %>% 
  mutate(cGR = CGR(Yearly_Growth))

Here, the custom function is not an accumulate function but just a custom one that calculates the same result. Both should be very fast though I think that the first one would be faster if there are millions of calculations to be done, though I still think that wouldn't be a big difference.

PJ

[Carol16]

Oh Dear! Thanks so much for your kind help and warm suggestions:) I don't know how to express my deep appreciation for your timely help and nice solutions!! Wish you a wonderful weekend and keep safe