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 = function (cf, d, tau = NULL, comp_freq = 1, interval = c(-1, 10), ...)
{
uniroot(xnpv, interval = interval, cf = cf, d = d, tau = tau,
comp_freq = comp_freq, extendInt = "yes", ...)$root
}
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:
xnpv = function (i, cf, d, tau = NULL, comp_freq = 1)
{
if (is.null(tau))
tau <- as.integer(d - d[1])/365
delta <- if (comp_freq == 0) {
1/(1 + i * tau)
}
else if (comp_freq == Inf) {
exp(-tau * i)
}
else {
1/((1 + i/comp_freq)^(tau * comp_freq))
}
sum(cf * delta)
}
We can visualize xnpv and its root as follows:
library(tvm)
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")
x = seq(-0.8, 10, 0.01)
y = sapply(x, function(x) xnpv(i = x, cf = v, d = d1, tau = as.integer(d1 - d1[1])/365))
plot(x, y, type = 'l', ylab = "xnpv", xlab = "cf"); abline(h = 0, lty = 2); abline(v = 0.1244512, lty = 2)
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:
offset = 0.001; comp_freq = 1
tvm::xirr(v, d1, lower = -comp_freq+offset, upper = 10, comp_freq = comp_freq, tol = 1e-7) # I also changed the numerical tolerance for increased accuracy.
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)).