I want to calculate the IRR (using the xirr function from package tvm) for each ID in a table where each ID has a different number of rows. I believe I have to use first occurrence to last occurrence-1, but after that, I am not sure what to do. Does anyone have any suggestions?
I have posted an example data frame below, for which I have tried using both the summarise function in dplyr with function xirr and to write a for loop. No success.
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 %>% group_by(ID) %>% summarise(
IRR = xirr(cf = exampledf$CashFlow, d = exampledf$Date, tau = NULL, comp_freq = 12, interval = c(-1, 10)))
The expected results should be something like:
ID IRR
1 2 0.127
2 3 0.125
Currently when running the summarise function it returns the same IRR for both ID's which should not be the case. My attempt with the for loop was not successful either, any help here would be appreciated!
We need to remove the example$ in summarise as the example$ will select the entire column instead of the 'CashFlow' within each 'ID'. In addition, the 'Date' column type should be changed to Date
library(dplyr)
library(tvm)
exampledf %>%
mutate(Date = as.Date(Date)) %>%
group_by(ID) %>%
summarise(
IRR = xirr(cf =CashFlow, d = Date,
tau = NULL, comp_freq = 12, interval = c(-1, 10)))
# A tibble: 2 x 2
# ID IRR
# <dbl> <dbl>
#1 2 0.121
#2 3 0.119