XIRR in JMP using R ?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 .
Re: XIRR in JMP using R ?@Dahlia_Watkins , @Justin_Chilton - Have either of you attempted XIRR in JMP ?
Re: XIRR in JMP using R ?@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.
Re: XIRR in JMP using R ?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.
