Calculate NPV for cashflows at all point in time

Question

I have the following data frame contains cash flows for several items. For example:

test <- data.frame(ID = c(rep("A",3), rep("B",4)), 
        time = c("y3","y2","y1","y4","y3","y2","y1"),
        Cfs= c(rep(1,3),rep(2,4)),
        interest = c(rep(0.1,3),rep(0.05,4)))

ID  time    CFs interest
A   y3      1   0.1
A   y2      1   0.1
A   y1      1   0.1
B   y4      2   0.05
B   y3      2   0.05
B   y2      2   0.05
B   y1      2   0.05

I would like to produce the net present value at each point in time for each item so the final output should look something like this:

ID  time   CFs  interest    NPV
A   y3     1    0.1         2.487
A   y2     1    0.1         1.736
A   y1     1    0.1         0.909
B   y4     2    0.05        7.092
B   y3     2    0.05        5.446
B   y2     2    0.05        3.719
B   y1     2    0.05        1.905

I was able to calculate the NPV of the total cash flows for each item by reading some of the old posts but I am not sure how to do that at each time period. Also, since the actual dataset is quite large (300k+), I am also trying to avoid loops.

Thanks

Answer 1

You may find some of these helper functions useful

dcf <- function(x, r, t0=FALSE){
  # calculates discounted cash flows (DCF) given cash flow and discount rate
  #
  # x - cash flows vector
  # r - vector or discount rates, in decimals. Single values will be recycled
  # t0 - cash flow starts in year 0, default is FALSE, i.e. discount rate in first period is zero.
  if(length(r)==1){
    r <- rep(r, length(x))
    if(t0==TRUE){r[1]<-0}
  }
  x/cumprod(1+r)
}

npv <- function(x, r, t0=FALSE){
  # calculates net present value (NPV) given cash flow and discount rate
  #
  # x - cash flows vector
  # r - discount rate, in decimals
  # t0 - cash flow starts in year 0, default is FALSE
  sum(dcf(x, r, t0))
}

Now, we can apply the power of dplyr

library(dplyr)

test %>% mutate_if(is.factor, as.character) %>% 
  arrange(ID, time) %>% 
  group_by(ID) %>% 
  mutate(DCF=cumsum(dcf(x=Cfs, r=interest)))

#> # A tibble: 7 x 5
#> # Groups:   ID [2]
#>      ID  time   Cfs interest       DCF
#>   <chr> <chr> <dbl>    <dbl>     <dbl>
#> 1     A    y1     1     0.10 0.9090909
#> 2     A    y2     1     0.10 1.7355372
#> 3     A    y3     1     0.10 2.4868520
#> 4     B    y1     2     0.05 1.9047619
#> 5     B    y2     2     0.05 3.7188209
#> 6     B    y3     2     0.05 5.4464961
#> 7     B    y4     2     0.05 7.0919010

Answer 2

The question is old, but I write my answer here, maybe it would be helpful for someone:

You need to compute NPV using cumsum() and cumprod() for each ID in a for() loop as below:

test <- test %>% mutate(npv = -1)

for(j in unique(test$ID)){
  x <- (test %>% filter(ID == j))$Cfs
  irr <- (test %>% filter(ID == j))$interest
  npv <- cumsum(x/cumprod(1+irr)) %>% round(3)
  test$npv[test$ID==j] <- npv[length(npv):1]
}

test

And the result is as below:

  ID time Cfs interest   npv
1  A   y3   1     0.10 2.487
2  A   y2   1     0.10 1.736
3  A   y1   1     0.10 0.909
4  B   y4   2     0.05 7.092
5  B   y3   2     0.05 5.446
6  B   y2   2     0.05 3.719
7  B   y1   2     0.05 1.905