CODEWITHSUNDEEP
javascriptjqueryexcelmathexcel-formula
dciso
dciso

Reputation: 1264

Javascript IRR (Internal rate of return) Formula Accuracy

I'm using a IRR function in javascript to create calculation a that is done in excel using its own IRR function. The problem is mine is little off and I have no idea why. Here's the code below.

var IRRval = [];

IRRval.push(-financed);
for (i = 0; i < period; i++) {
    IRRval.push(rental);
}

var IRR = IRRCalc(IRRval, 0.001) * 0.01;

function IRRCalc(CArray, guest) {
    inc = 0.000001;
    do {
        guest += inc;
        NPV = 0;
        for (var j=0; j < CArray.length; j++) {
            NPV += CArray[j] / Math.pow((1 + guest), j);
        }
    } while (NPV > 0);
    return guest * 100;
}

Now if you use these figures:

Period 24

Financed 22000

Rental 1017.5000

My Result is: 0.008523000000000175

Excel Result is: 0.008522918

OR

Period 42

Financed 218000

Rental 5917.1429

My Result is: 0.006247000000000489

Excel Result is: 0.00624616

The Excel function is called: =IRR(T12:T73,0.01) T12-T73 is the same figures I'm using.

Any help would be much appreciated, Thanks Jason

UPDATE

I've solved it by changing the values below. But now the performance is too slow. Any ideas on how to improve this?

IRRCalc(IRRval, 0.001)

//to

IRRCalc(IRRval, 0.0001)

inc = 0.000001;

//to

inc = 0.00000001;

Upvotes: 9

Views: 19922

Answers (8)

MXLange
MXLange

Reputation: 1

Here is an optimized cote to calculate IRR

This starts incrementing in 1 and if the algo reaches negative it change signal and divide increment by 10, now it decrements in 0.1 and now if it goes to positive change signal again divides by 10,and now it increments again in 0.01, it goes on till react temp between -100 and 100, wuat guarants a precise IRR.

function tirCalc(amounts, investment) {
    // This algorithm goes up and down till reach zero.
    let tir = 0.0;
    let result = false;
    let dividend = 100;
    let actualSignal = 1; //positive, means tir is increasing

    if (investment > 0) investment *= -1;
    do {
        let temp = 0;
        //Calculate tir based on the formula
        for (let i = 0; i < amounts.length; i++) {
            temp += amounts[i] / Math.pow(1 + tir, i + 1);
        }
        temp += investment;

        //Checks if tir is zero, zero is the expected result
        if (temp < 100 && temp > -100) {
            result = true;
            break;
        }

        //During calculation this code change the sum direction and rate changing the signal and dividend size to reduce the increment
        if ((temp < -100 && actualSignal > 0) || (temp > 100 && actualSignal < 0)) {
            actualSignal *= -1;
            dividend /= 10;
        }
        if (actualSignal > 0) {
            tir += dividend / 100;
        }
        if (actualSignal < 0) {
            tir -= dividend / 100;
        }
    } while (result === false);
    return (tir * 100).toFixed(2);
}

Upvotes: 0

Zulfugar Ismayilzadeh
Zulfugar Ismayilzadeh

Reputation: 2763

Try this.

function NPV(discountRate, cashFlow){
    var npv = 0;
    for(var t = 0; t < cashFlow.length; t++) {
        npv += cashFlow[t] / Math.pow((1+ discountRate),t);
    }
    return npv;
}


function IRR(cashFlow,guess){
    guess = guess ? guess : 0.1;
    var npv;
    do
    {
        npv = NPV(guess,cashFlow);
        guess+= 0.001;

    }
    while(npv > 0)

    return guess;
}

Upvotes: 2

Lim Han Yang
Lim Han Yang

Reputation: 440

This might be a better way of writing the calculation. This allows the code to calculate instances of negative IRR or IRR above 100%.

        var IRRval = [];

        IRRval.push(-financed);
            for (i = 0; i < period; i++) {
            IRRval.push(rental);
        }
        var IRR = IRRCalc(IRRval, 0.001) * 0.01;

        function IRRCalc(CArray) {
            var r = 0
            min = -1.0;
            max = 10000.0;
            do {
                guest = (min + max) / 2;
                NPV = 0;
                for (var j = 0; j < CArray.length; j++) {
                    NPV += CArray[j] / Math.pow((1 + guest), j);
                }
                if (NPV > 0) {
                    min = guest;
                }
                else {
                    max = guest;
                }
                r++
            } while (r < 100);
            return guest * 100
        }

Upvotes: 0

Set F
Set F

Reputation: 1

Don't set your min IRR to 0. That is what bounds your answer. Try allowing it to go negative. Also don't try to calc an IRR on one number.

Upvotes: 0

HattrickNZ
HattrickNZ

Reputation: 4653

I would like to build on from @Zohaib Answer, but what I would like to do is show undefined where appropriate. The best I can do is get it to equal to zero. I am using this simple dataset irr_arr=[-100, 100, 100, 100, 100,100]. I would appreciate some advice.

//IRRCALC funtion that handles irr going to infinity 

function IRRCalc_test(CArray) {

  min = 0.0;
  max = 1.0;
  c=0;
  do {
    guest = (min + max) / 2;
    NPV = 0;
    for (var j=0; j<CArray.length; j++) {
          NPV += CArray[j]/Math.pow((1+guest),j);
    }
    if (NPV > 0) {
      min = guest;
      c++; 
    }
    else {
      max = guest;
      c++;
    }

    if(c>=15){ return guest*100; }
  } while(Math.abs(NPV) > 0.000001);
  return guest*100;
}


// some testing 

irr_arr=[-100, 100, 100, 100, 100,100]
irr_res_arr_expected=[0,0,61.8,83.93,92.76,96.6]
for(i=1;i<=irr_arr.length;i++){
console.log("irr_arr - ",irr_arr.slice(0,i));
console.log("IRRCalc - ",IRRCalc(irr_arr.slice(0,i)))
//console.log("irr_expected - ", irr_res_arr_expected[i-1])
//if(IRRCalc(irr_arr.slice(0,i))===parseFloat(irr_res_arr_expected[i-1]).toFixed(2)){console.log(i,"- TRUE")} else {console.log(i,"- FALSE")}
}

this is the output 

irr_arr -  [-100]
IRRCalc -  0.00      <<<<<<<<<<<<<<<<<<<------- this should be 'undefined' and not 'zero'
irr_arr -  [-100, 100]
IRRCalc -  0.00
irr_arr -  [-100, 100, 100]
IRRCalc -  61.80
irr_arr -  [-100, 100, 100, 100]
IRRCalc -  83.93
irr_arr -  [-100, 100, 100, 100, 100]
IRRCalc -  92.76
irr_arr -  [-100, 100, 100, 100, 100, 100]
IRRCalc -  96.60

here is the excel of what I am trying to produce

enter image description here

Upvotes: 1

Zohaib
Zohaib

Reputation: 496

We modified the code to achieve performance and accuracy. Try this:

function IRRCalc(CArray) {

  min = 0.0;
  max = 1.0;
  do {
    guest = (min + max) / 2;
    NPV = 0;
    for (var j=0; j<CArray.length; j++) {
          NPV += CArray[j]/Math.pow((1+guest),j);
    }
    if (NPV > 0) {
      min = guest;
    }
    else {
      max = guest;
    }
  } while(Math.abs(NPV) > 0.000001);
  return guest * 100;
}

Upvotes: 12

Ben Lesh
Ben Lesh

Reputation: 108491

It's a floating point issue to be sure. You're going to want to use a library like BigDecimal.js to handle your values. It's really the only way to avoid this issue.

Upvotes: 1

whitneyit
whitneyit

Reputation: 1226

After a quick skim read through your code, the error seems to be related to floating point precision error. More information can be found here: http://ajaxian.com/archives/crock-on-floating-points-in-javascript

In older javascript engines, if you did 0.3 + 0.3 you get something like 0.600000000001

Though most javascript engines today return 0.6, under the hood, the problem remains. Adding floats together causes unexpected results. So in your case the

inc = 0.000001;
guest += inc;

seems to me, to be the problem.

A way to solve this would be to use whole numbers. So instead of 0.000001 you would use 1 and instead of 0.001 you would use 1000. Then divide your return result by 100000

Upvotes: 10

Related Questions