XIRR Function

XIRR.js

// Copyright (c) 2012 Sutoiku, Inc. (MIT License)
 
// Some algorithms have been ported from Apache OpenOffice:
 
/**************************************************************
 * 
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 * 
 *************************************************************/

function XIRR(values, dates, guess) {
  // Credits: algorithm inspired by Apache OpenOffice
  
  // Calculates the resulting amount
  var irrResult = function(values, dates, rate) {
    var r = rate + 1;
    var result = values[0];
    for (var i = 1; i < values.length; i++) {
      result += values[i] / Math.pow(r, moment(dates[i]).diff(moment(dates[0]), 'days') / 365);
    }
    return result;
  }

  // Calculates the first derivation
  var irrResultDeriv = function(values, dates, rate) {
    var r = rate + 1;
    var result = 0;
    for (var i = 1; i < values.length; i++) {
      var frac = moment(dates[i]).diff(moment(dates[0]), 'days') / 365;
      result -= frac * values[i] / Math.pow(r, frac + 1);
    }
    return result;
  }

  // Check that values contains at least one positive value and one negative value
  var positive = false;
  var negative = false;
  for (var i = 0; i < values.length; i++) {
    if (values[i] > 0) positive = true;
    if (values[i] < 0) negative = true;
  }
  
  // Return error if values does not contain at least one positive value and one negative value
  if (!positive || !negative) return '#NUM!';

  // Initialize guess and resultRate
  var guess = (typeof guess === 'undefined') ? 0.1 : guess;
  var resultRate = guess;
  
  // Set maximum epsilon for end of iteration
  var epsMax = 1e-10;
  
  // Set maximum number of iterations
  var iterMax = 50;

  // Implement Newton's method
  var newRate, epsRate, resultValue;
  var iteration = 0;
  var contLoop = true;
  do {
    resultValue = irrResult(values, dates, resultRate);
    newRate = resultRate - resultValue / irrResultDeriv(values, dates, resultRate);
    epsRate = Math.abs(newRate - resultRate);
    resultRate = newRate;
    contLoop = (epsRate > epsMax) && (Math.abs(resultValue) > epsMax);
  } while(contLoop && (++iteration < iterMax));

  if(contLoop) return '#NUM!';

  // Return internal rate of return
  return resultRate;
}

thank you sooo much for saving time 😊

After doing R&D i got it how to use it.
We need moment.js library loaded in our app before we use this XIRR function.

Really thank you so much for saving time.
Great Job.

Awesome Gist, thanks a lot!

P.S. For some reason the difference in days calculated by moment.js is one lower than would be expected. i.e.

moment(2016, 9, 10).diff(moment(2016, 9, 5), 'days') gives out 4. Just in case anyone else is wondering.

Much thanks!

Hi Please Help me I have all the values and dates but It's not working for me. anyone have a running example so I can get the actual problem.

need urgent to solve, please help me

In 2020 let p = moment("2016/9/10", 'YYYY/MM/DD').diff(moment("2016/9/5", 'YYYY/MM/DD'), 'days'); gives you 5 @wocoburguesa

@Bhoomimehta, This is how you execute the code. Requires moment.js.

let money = [-100,20,25,30,35,40],
    dates = [moment("2017/6/30", 'YYYY/MM/DD'),moment("2017/12/31", 'YYYY/MM/DD'),
                    moment("2018/12/31", 'YYYY/MM/DD'),moment("2019/12/31", 'YYYY/MM/DD'),
                    moment("2020/12/31", 'YYYY/MM/DD'),moment("2021/12/31", 'YYYY/MM/DD')];
let j = XIRR(money,dates) * 100;
console.log(j);

@thorst Hi needed some help , when I try your code it works fine. but with my values it does not work. can you look into this ? These are my values :
let money = [-3865655.40,19708.41,20925.61,22142.82,23360.02,24577.23],
dates = [moment("2020/11/02",'YYYY/MM/DD'),moment("2020/121/02",'YYYY/MM/DD'),
moment("2020/01/02",'YYYY/MM/DD'),moment("2020/02/02",'YYYY/MM/DD'),
moment("2020/03/02",'YYYY/MM/DD'),moment("2020/04/02",'YYYY/MM/DD')];
let j = XIRR(money,dates,0.15) * 100;
console.log(j);

this gives NaN output.
Any help will be appreciated thanks!

@abhii1012
moment("2020/121/02",'YYYY/MM/DD') -> This needs to be correct like moment("2020/12/02",'YYYY/MM/DD')

Hey, @ghalimi thanks for the script, it's a saver. A little fix is required due to the changed moment.diff() behaviour:

https://momentjs.com/docs/#/displaying/difference/
By default, moment#diff will truncate the result to zero decimal places, returning an integer. If you want a floating point number, pass true as the third argument. Before 2.0.0, moment#diff returned a number rounded to the nearest integer, not a truncated number.

Sometimes, depending on the dates and timezones it returns not the expected results and the XIRR differs from what we get in Excel or Google Sheets. Lines 34 and 44 should have:
Math.round(moment(dates[i]).diff(moment(dates[0]), 'days', true))
instead of:
moment(dates[i]).diff(moment(dates[0]), 'days')

@wocoburguesa This might be the reason for your issue.

@ghalimi Thanks for the script!

Why this dataset returns NaN?

const values = [
  -1.00250692,
  -100.250692,
  -1.00250692,
  -100.250692,
  53.49105162911925,
  32.55768506535615
]
const dates = [
  "2021-07-09",
  "2021-07-09",
  "2021-07-09",
  "2021-07-09",
  "2022-02-25",
  "2022-02-25"
]
XIRR(values, dates); // NaN

Found replacement package
https://github.com/eric-malachias/irr