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

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