CODEWITHSUNDEEP
rexcel
SBravo
SBravo

Reputation: 13

XIRR function from tvm package delivers different outcome than XIRR from Excel

I'm trying to calculate IRR from the below cash flows and get slightly different results using tvm::xirr compared to XIRR from Excel.

Dates= c("2020-12-31", "2021-12-31")

CF = c(-18965299.53, 18884929.89)

library(tvm)
xirr(CF, Dates, comp_freq = Inf)

= -0.004270912

versus -0.00423772 using Excel (the result does not match even if I modify comp_freq).

This is a simplified example but the discrepancy becomes bigger in other cases.

Does someone know how to adjust the formula so that it gives same result as Excel?

I know I can build my own code to calculate IRR, but I'd prefer to use this function, as I have a huge database with irregular daily cash flows.

Thanks

Upvotes: 1

Views: 523

Answers (1)

Fernando Barbosa
Fernando Barbosa

Reputation: 1134

I read the documentation both from TVM and Excel XIRR. If you use the same arguments, you will find the same result (you can choose the precision level).

My code to replicate excel in R is:

library(tvm)
Dates= c("2020-12-31", "2021-12-31")
DatesReal= as.Date(Dates)
CF = c(-18965299.53, 18884929.89)
xirr(CF, DatesReal, comp_freq = 1, maxiter=100, tol=0.00000001)

TVM utilize an algorithm named uniroot to find the IRR. Therefore you can include arguments such as the maximum numbers of iterations to find the result (maxiter) and the desired accuracy/convergence tolerance (tol).

Excel also use an interactive technique. XIRR cycles through the calculation until the result is accurate within 0.000001 percent (equivalent to tol=0.00000001) and has a limit of 100 iterations (equivalent to maxiter=100).

The result for both techniques in your example is -0.00423772.

Upvotes: 1

Related Questions