[IRR-Question] Excel can generate result and finance.js can't

[guilhermedecampo]

Hi @essamjoubori nice effort to write this package! Pretty awesome!

I'm a bit new to this world and I'm having some difficult to understand why excel can do IRR with some values and finance.js can't. I'm guessing it's because javascript itself can't deal with imaginary numbers.

finance.js

excel

Please let me know your thoughts on how to overcome that. Thank you!

[parikhishan]

Hi @guilhermedecampo

Did you ever figure out a solution for this issue ? I am facing the same issue when we have the first years (-neg) value still greater(i.e. not above 0) then addition for the rest of the years (Just as shown in your example). The excel gives value of IRR in -neg like -15.33% but the finance.js does not return anything and it is empty.

Let me know if you have fixed in with tweak or anything.

Thanks Ishan

[guilhermedecampo]

Yes @parikhishan we fixed with tweaks in the cashflow data creating a wrapper around the XIRR function.

Explanations:

1. I don't want to pollute my error logs so I first check for emptiness of important data
2. There is a small catch that works when cashflow starts or ends in 0
3. We try the usual way with the guessing of 0.001
4. If it gives me NaN or Infinity we try a different guess by dividing total positive per total negative
5. We use the catch Number(Math.round(irr + 'e4') + 'e-4') to round the number with 4 decimals

Checkout the code below for more details.

import { Finance } from 'financejs'
import sum from 'lodash/sum'
import isEmpty from 'lodash/isEmpty'

import getListBySignal from '/imports/fn/filter-by-signal'

const finance = new Finance()

export default function IRR({ cashflows, dates }) {
  // Checks for right data
  if (
    isEmpty(cashflows) ||
    isEmpty(dates) ||
    isEmpty(getListBySignal(cashflows, 'positive')) ||
    isEmpty(getListBySignal(cashflows, 'negative'))
  )
    return

  let cash = cashflows

  if (Math.abs(cashflows[0]) === 0) {
    cash = [-0.0001, ...cashflows.slice(1)]
  }

  if (Math.abs(cashflows[cashflows.length - 1]) === 0) {
    cash = [...cashflows.slice(0, -1), 0.0001]
  }

  let irr

  try {
    irr = finance.XIRR(cash, dates, 0.0001) / 100
  } catch (error) {
    // console.log('---------------------')
    // console.error('xirr error ->', error)
    // console.log('cashflows', cash)
    // console.log('dates', dates)
    // console.log('\n\n\n\n---------------------')
  }

  // Try with custom guess
  if (isNaN(irr) || !isFinite(irr)) {
    try {
      const guess = Math.abs(sum(getListBySignal(cash)) / sum(getListBySignal(cash, 'negative'))) - 1

      irr = finance.XIRR(cash, dates, guess) / 100
    } catch (error) {
      console.log(error, '---------- IRR custom guess did not work-----------')
    }
  }

  return Number(irr) && !isNaN(irr) && isFinite(irr) ? Number(Math.round(irr + 'e4') + 'e-4') : null
}

[guilhermedecampo]

If you find anything else to make it more reliable please let me know @parikhishan =)

[parikhishan]

Hi @guilhermedecampo

Thanks for your prompt response. I see you have included a lot of lib apart from Finance. I have currently imported only the finance library. From the code above i feel the following part is most necessarily important for fixing the issue with -neg IRR return. Hope i am correct on that.

// Try with custom guess if (isNaN(irr) || !isFinite(irr)) { try { const guess = Math.abs(sum(getListBySignal(cash)) / sum(getListBySignal(cash, 'negative'))) - 1

  irr = finance.XIRR(cash, dates, guess) / 100
} catch (error) {
  console.log(error, '---------- IRR custom guess did not work-----------')
}

}

return Number(irr) && !isNaN(irr) && isFinite(irr) ? Number(Math.round(irr + 'e4') + 'e-4') : null

Is it possible to just edit the existing version of Finance.IRR function and include the above, without much importing new libraries?

Thanks Ishan

[parikhishan]

This is my code at the moment in Javascript function. Finance.prototype.IRR = function(cfs) { var args = arguments; var numberOfTries = 1; // Cash flow values must contain at least one positive value and one negative value var positive, negative; Array.prototype.slice.call(args).forEach(function (value) { if (value > 0) positive = true; if (value < 0) negative = true; }); if (!positive || !negative) throw new Error('IRR requires at least one positive value and one negative value'); function npv(rate) { numberOfTries++; if (numberOfTries > 1000) { throw new Error('IRR can't find a result'); } var rrate = (1 + rate/100); var npv = args[0]; for (var i = 1; i < args.length; i++) { npv += (args[i] / Math.pow(rrate, i)); } return npv; } return Math.round(seekZero(npv) * 100) / 100; };

[egodigitus]

Was setting
if (numberOfTries > 1000) in the IRR implementation of the lib to a greater number. 10000 seems to work good me for