Reproducible example:
v <- c(-400000.0,-200000.0, 660636.7)
d <- c("2021-10-27","2022-12-23","2023-01-04")
d1 <- as.Date(d, format="%Y-%m-%d")
tvm::xirr(v, d1) # gives the error below
Error in uniroot(xnpv, interval = interval, cf = cf, d = d, tau = tau, :
f.lower = f(lower) is NA
Excel XIRR returns 0.125 which seems correct.
The uniroot documentation says "Either interval or both lower and upper must be specified", and I'm not sure if tvm::xirr does so. I guess it does because it works well for many other sets of data.
Anyway, I could get it to work correctly in this case by providing a lower and upper (now that I know the answer via Excel) with some trial and error as below. But I'm not sure if my bounds will always hold.
> tvm::xirr(v, d1, f.lower = -0.2, f.upper=0.5)
[1] 10
> tvm::xirr(v, d1, f.lower = -0.2, f.upper=5)
[1] -1
> tvm::xirr(v, d1, lower = -0.99, upper=0.99)
[1] 0.1244512
Is this a bug or limitation of tvm::xirr or am I missing something?
Let us go down the rabbit hole. Firstly, let us read the source code for tvm::xirr:
Xirr calls uniroot to identify at what cf the function xnpv is equal to zero in the interval c(-1, 10). Default parameter values are tau = NULL and comp_freq = 1. Secondly, let us see the source code for xnpv:
We can visualize xnpv and its root as follows:
As you can see, for comp_freq = 1, the factor 1/(1 + i/comp_freq) (in the definition of delta) has a vertical asymptote at i = -1 for exponents different than 0 (0^0 = 1 in R). Moreover, for i < -1, this expression is undefined in R (negative number raised to decimal powers equals NaN in R).
To solve this issue, assuming comp_freq different than 0 or +Inf, you can call xirr as follows:
This assumes that cf <= 10. Finally, given that comp_freq = 1 is the default value, xirr always fails under default settings (thus: this function has not been thoroughly tested by its developer(s)).