Unable to use XIRR function with moving window

All,
I am trying to calculate xirr from the tvm library. Provided below is a reproducible example :

# Reprex
# Attach desired packages 
suppressPackageStartupMessages(library(tvm))

# Provide input data 
CF <- c(-7500, 3000, 5000, 1200, 4000)
d <- as.Date(c("2016-01-01", "2016-02-01", "2016-04-15", "2016-08-01", "2017-03-26"))

# Call the XIRR Function
Res1 <- vector()
Res1 <- sapply(1:length(d), function(i) { tryCatch({ expr = xirr(i, CF[i:length(CF)],
                d[i:length(d)])
                }, error = function(i) { return(NA) })
})

# Print Results 
print(Res1)

The code returns NA while the exact same data returns a valid value in Excel as shown here (MS Excel: How to use the XIRR Function (WS)) . Any guidance would be appreciated

This?

> xirr(CF, d)
[1] 2.660267

Thank you and a follow up question.

If I wanted to implement a moving window, so for the first step, the entire CF vector is provided as input and for the second iteration , values starting from i = 2 and so on , how would I modify the code I have?

I should have made that clear upfront. Apologies

I don't think that's possible. Because you need to have a sign change in the cash flow values and there isn't a sign change after you've dropped the first value.

For example,

library(tvm)

# Provide input data 
CF <- c(-7500, 3000, 5000, 1200, 4000)
d <- as.Date(c("2016-01-01", "2016-02-01", "2016-04-15", "2016-08-01", "2017-03-26"))

xirr(CF[2:5], d[2:5])
#> Error in uniroot(xnpv, interval = interval, cf = cf, d = d, extendInt = "yes", : no sign change found in 1000 iterations
1 Like

@mhenderson,
Can you help me correct the following in such a way that the function returns "NA" when there is an error , else returns a value ? Based on your input , I understand that the function needs to be re-written and I tried it below. Currently it returns just "NA"

# Reprex
# Attach desired packages 
suppressPackageStartupMessages(library(tvm))

# Provide input data 
CF <- c(-78662, -32491, -32492, 7651, 40300)
d <- as.Date(c("2019-06-30", "2019-09-30", "2019-12-31", "2020-03-31", "2020-06-30"))

# Call the XIRR Function
Res1 <- vector()
Res1 <- sapply(1:length(d), function(i) {
   tryCatch({
       expr = xirr(i, CF[1:i],
               d[1:i])
   }, error = function(i) { return(NA) })
})

In the above code, by changing the inputs , I am ensuring that the code should return a value starting at i = 4 , because at i = 4, I would expect to see a value since there is a sign change I expect to have a value. Similarly at i =5.

Hi @Uday - heads up, [at]support is reserved here for support with rstudio's pro products. I am going to hide this part of your reply.