1

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

Dave2e
  • 22,192
  • 18
  • 42
  • 50
SBravo
  • 13
  • 2

1 Answers1

0

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.

Fernando Barbosa
  • 853
  • 1
  • 8
  • 24
  • Thank you Fernando for your reply. This works indeed. Now I'm facing the following error when using xirr function from TVM package for another set of cash flows. I'm using the exact code you provided. Do you know how can I fix this? Error in uniroot(xnpv, interval = interval, cf = cf, d = d, tau = tau, : f.lower = f(lower) is NA – SBravo Apr 29 '22 at 17:03
  • I will investigate for you. Can you find the result for this cashflow in Excel? Also, it is useful if you can share the data. – Fernando Barbosa Apr 29 '22 at 19:07
  • Sure. I have just accepted the answer. Also, please see below for an example. XIRR() in Excel gives 69.91% but tvm returns "Error in uniroot(xnpv, interval = interval, cf = cf, d = d, tau = tau, : f.lower = f(lower) is NA". Thanks for your help ! Date CF 12/31/2020 (4,098,743,000) 4/9/2021 103,010,000 4/12/2021 221,990,000 7/9/2021 (0) 9/28/2021 (1,340,000,000) 12/23/2021 (1,300,000,000) 12/31/2021 9,338,515,000 – SBravo May 01 '22 at 03:59
  • Thank you! Your cashflow may be generating a weird function to optimize--to find the XIRR. If you input that your result will be positive (lower = 0), tvm can find the root--> xirr(CF, DatesReal, comp_freq = 1, maxiter=100, tol=0.00000001, lower = 0, upper = 10) – Fernando Barbosa May 01 '22 at 15:13
  • btw, any "lower>-1" works well for your cashflow. xirr(CF, DatesReal, comp_freq = 1, maxiter=100, tol=0.00000001, lower = -0.999, upper = 10) – Fernando Barbosa May 01 '22 at 15:16