CODEWITHSUNDEEP
javascriptxirr
Wogan
Wogan

Reputation: 1335

Why am I getting NaN from this XIRR function?

This one is driving me up the wall.

UPDATE: Assembled in a jsfiddle that yields NaN: https://jsfiddle.net/eqcww2y7/6/

I'm using this XIRR function: https://gist.github.com/ghalimi/4669712

And into that function, I'm sending a simple pair of arrays - 4 dates, 4 cashflow values. 

var dates = [
  "2016-01-01",
  "2016-02-01",
  "2016-03-01",
  "2016-04-01",
];

var values = [
  -1000000,
  0,
  0,
  750000
];

console.log('dates', dates);
console.log('values', values);
console.log(XIRR(values, dates, 0.1));

The function has a dependency on momentjs, which is not causing any issues - console.log() inside the function shows the dates are being parsed correctly.

That snippet above returns NaN, when it should return -0.68. If I run those exact same values through Excel:

Testing XIRR in Excel

Can anyone spot the issue? Is it with the function, am I sending the wrong values in?

Upvotes: 0

Views: 1212

Answers (2)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196187

Just to add to the already given explanations, the XIRR function used in that gist mentions that

Some algorithms have been ported from Apache OpenOffice

If you set up the same values/dates in OpenOffice Calc you get

enter image description here

Searching for this error in relation to XIRR in the openoffice forums you get to https://forum.openoffice.org/en/forum/viewtopic.php?t=14006 where they mention that the likely problem is what we have identified here.

Solution

They do offer a solution though (turns out it was a solution to specific constraints),

I suggest that you use SUM(C4:C15)/C3 (sum of the deposited values minus the received value) as a guess

In your case that would be SUM(B2:B4)/B1 and it indeed returns -0.684592

Upvotes: 0

Wogan
Wogan

Reputation: 1335

@Andreas got to the heart of it. This XIRR function was written in 2012, and as of ECMAScript 2015, Math.pow() behaves differently. I'm not sure if it's an issue that applies to all browsers/engines/implementations, but for my specific use case, XIRR() is returning NaN because the base and exponent inputs are going negative.

UPDATE: To clarify - in my use case, the base/exponent pairs start out like this:

x = 1.1, y = 0.0849
x = 1.1, y = 1.2493

But one iteration later I have this:

x = -0.512, y = 0.084
x = -0.512, y = 1.164

Since the x is a negative non-integer, I'm always gonna get NaN, no matter what the exponent is. At least that's my understanding of the limitations of Math.pow().

Upvotes: 0

Related Questions