rirrxirr

Remove all rows for a key with no sign change in a specific variable


I am trying to run an xirr function on several ID's but I get an error message saying:

        Error in uniroot(xnpv, interval = interval, cf = cf, d = d, tau = tau, :
no sign change found in 1000 iterations

Is there any way to remove all rows for the ID's that do not have a sign change (as ID 2 in the example below)?

library(tvm)
library(dplyr)

exampledf<-data.frame(c(2, 2, 2, 3, 3, 3, 3, 3), c("2017-11-30", "2017-12-31", "2018-01-31", "2017-11-30", "2017-12-31", "2018-01-31", "2018-02-28", "2018-03-31"), c(65000, 33000, 33000, -40000, 10250, 10250, 10000, 10500))
names(exampledf)<-c("ID","Date","CashFlow")
exampledf$Date <- as.Date(exampledf$Date)

exampledf %>%
  group_by(ID) %>% 
  summarise(
    IRR = xirr(cf = CashFlow, d = Date, 
               tau = NULL, comp_freq = 12, interval = c(-1, 10)))

Any help with this would be appreciated!


Solution

  • Combining the any() and sign() functions in the following way will test whether any of the values in column CashFlow are positive (any(sign(CashFlow) == 1) and negative (any(sign(CashFlow) == -1))

    library(dplyr)
    
    exampledf %>% 
        group_by(ID) %>% 
        filter(any(sign(CashFlow) == 1) && any(sign(CashFlow) == -1))
    

    Result

        # A tibble: 5 x 3
        # Groups:   ID [1]
             ID Date       CashFlow
          <dbl> <fct>         <dbl>
        1     3 2017-11-30   -40000
        2     3 2017-12-31    10250
        3     3 2018-01-31    10250
        4     3 2018-02-28    10000
        5     3 2018-03-31    10500