XIRR in JMP using R ?

Question

All, 
    I have been looking for a solution to handle XIRR calculation in JMP. In the process,  @ron_horne was kind enough toi share some of his work.

I went down the path of leveraging some packages within R for the calculation. I have also put in a feature request for XIRR and XNPV functions to be deployed into future versions of JMP. However, the current problem that I have noticed is - the R package that I am leveraging is not yielding results I expect and it is kind of confusing me. Wondering if anyobdy like @ih or any other JMP users on the community have seen success going down this path or ventured out in a different direction with success for XIRR calculation.

RConn = R Connect();
ConnFlag = R Is Connected();

If(ConnFlag,
 R Submit("
 # 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) })
 })

# Print Results 
 #print(Res1)
 "); 
 Results = R Get(Res1); 
RConn << disconnect; 
 );

The above calculation should return NA until i = 4 , but once the cash flow sees a sign change - it should return a value other than NA as Excel would. Any thoughts or guidance to fix this is appreciated .

uday_guntupalli · Answer

@Dahlia_Watkins , @Justin_Chilton - Have either of you attempted XIRR in JMP ?

uday_guntupalli · Answer

@ih, 
    I have posted only a sample of the cash flow as an example. However, I see the value of your advice, because I do see an error when sapply is not used.  The uniroot error pops up and I thought it was because of the -ve cash flow alone. In order to circumvent that I put the trycatch in place. 
   What is interesting though is Excel is able to tackle these and provide results. But thank you for the advice - will look through their repository too.

ih · Answer

Hi Uday,

Unfortunatelly we use our own IRR calculate so I am not much help with the R function.  That said, the cash flows in your project are all so negative that you would need an incredibly large negative IRR to make that project's NPV zero.  You likely need more positive cash flows for the solver to converge (IRR is not calculated directly, instead you find the time value of money that sets NPV equal to zero).

Note that wrapping the xirr function call with sapply can mask errors, I bet if you call xirr directly you will see an error related to a solver.

It looks like the nvm package is mirrored here, that might help you understand what the package is doing.  For example, you could use their definition of xnpv to manually find the irr by adjusting i:

i <- .01
sum(CF / ((1 + i) ^ (as.integer(d - d[1]) / 365)))

JMP has a equation solver so building this in JSL should be possible, I have not played with it though.