CODEWITHSUNDEEP
rfor-loopreturnfinancepanel-data
student123
student123

Reputation: 11

How to divide a value by the same value in the previous year in R? (Return calculation)

I have a panel data set with approx 4000 companies over 13 years. I want to calculate the return on these companies through the formula below. The Price (P) I'm using is the firm's Closing Price, adjusted.

rt = ( P(t=1)/ P(t= t-1) ) - 1

I want to create a new column for this return. Does anybody have any experience with how to divide a value (in this case the closing price (P)), on the closing price the year before for all the companies without making it too complicated?

Thank you.

Upvotes: 1

Views: 164

Answers (1)

Helix123
Helix123

Reputation: 3687

You will need to work with commands and/or data structures which are aware of the panel structure of your data. One way is to use package plm and its pdata.frame data structure. Then you can easily compute lagged values and calculate returns per firm.

See the example below and let's assume the contained data set Grunfeld and its column value is the the Closing Price P you want to compute the return for as per your formula (ret in the example below).

You see that the function lag in package plm respects the panel structure of the data when looking at the output.

library(plm)
data("Grunfeld", package = "plm")

# print some lines to get an idea about the data
# first 3 lines for first 3 individuals
selected.obs <- c(1:3, 21:23, 41:43)
Grunfeld[selected.obs, ]
#>    firm year   inv  value capital
#> 1     1 1935 317.6 3078.5     2.8
#> 2     1 1936 391.8 4661.7    52.6
#> 3     1 1937 410.6 5387.1   156.9
#> 21    2 1935 209.9 1362.4    53.8
#> 22    2 1936 355.3 1807.1    50.5
#> 23    2 1937 469.9 2676.3   118.1
#> 41    3 1935  33.1 1170.6    97.8
#> 42    3 1936  45.0 2015.8   104.4
#> 43    3 1937  77.2 2803.3   118.0

# make data a pdata.frame (panel-aware data frame)
pGrun <- pdata.frame(Grunfeld, index = c("firm", "year"))

# compute lag
pGrun$lvalue <- plm::lag(pGrun$value)

# compute return as per formula:
pGrun$ret <- (pGrun$value / pGrun$lvalue) - 1

# print first 3 lines for first 3 individuals
pGrun[selected.obs, ]
#>        firm year   inv  value capital lvalue       ret
#> 1-1935    1 1935 317.6 3078.5     2.8     NA        NA
#> 1-1936    1 1936 391.8 4661.7    52.6 3078.5 0.5142764
#> 1-1937    1 1937 410.6 5387.1   156.9 4661.7 0.1556085
#> 2-1935    2 1935 209.9 1362.4    53.8     NA        NA
#> 2-1936    2 1936 355.3 1807.1    50.5 1362.4 0.3264093
#> 2-1937    2 1937 469.9 2676.3   118.1 1807.1 0.4809916
#> 3-1935    3 1935  33.1 1170.6    97.8     NA        NA
#> 3-1936    3 1936  45.0 2015.8   104.4 1170.6 0.7220229
#> 3-1937    3 1937  77.2 2803.3   118.0 2015.8 0.3906638

Upvotes: 1

Related Questions