How to use Javascript class for xirr formula

Question

Hello I have found a javascript class for some excel functions but i dont know how to use it. I need to use XIRR function but i dont know the type and format of parameters and the syntax.

Here is the code:

/* Based on 
 * - EGM Mathematical Finance class by Enrique Garcia M. <egarcia@egm.co>
 * - A Guide to the PMT, FV, IPMT and PPMT Functions by Kevin (aka MWVisa1)
 */

var ExcelFormulas = {

    PVIF: function(rate, nper) {
        return Math.pow(1 + rate, nper);
    },

    FVIFA: function(rate, nper) {
        return rate == 0? nper: (this.PVIF(rate, nper) - 1) / rate;
    },  

    PMT: function(rate, nper, pv, fv, type) {
        if (!fv) fv = 0;
        if (!type) type = 0;

        if (rate == 0) return -(pv + fv)/nper;

        var pvif = Math.pow(1 + rate, nper);
        var pmt = rate / (pvif - 1) * -(pv * pvif + fv);

        if (type == 1) {
            pmt /= (1 + rate);
        };

        return pmt;
    },

    IPMT: function(pv, pmt, rate, per) {
        var tmp = Math.pow(1 + rate, per);
        return 0 - (pv * tmp * rate + pmt * (tmp - 1));
    },

    PPMT: function(rate, per, nper, pv, fv, type) {
        if (per < 1 || (per >= nper + 1)) return null;
        var pmt = this.PMT(rate, nper, pv, fv, type);
        var ipmt = this.IPMT(pv, pmt, rate, per - 1);
        return pmt - ipmt;
    },

    DaysBetween: function(date1, date2) {
        var oneDay = 24*60*60*1000;
        return Math.round(Math.abs((date1.getTime() - date2.getTime())/oneDay));
    },

    // Change Date and Flow to date and value fields you use
    XNPV: function(rate, values) {
        var xnpv = 0.0;
        var firstDate = new Date(values[0].Date);
        for (var key in values) {
            var tmp = values[key];
            var value = tmp.Flow;
            var date = new Date(tmp.Date);
            xnpv += value / Math.pow(1 + rate, this.DaysBetween(firstDate, date)/365);
        };
        return xnpv;
    },

    XIRR: function(values, guess) {
        if (!guess) guess = 0.1;

        var x1 = 0.0;
        var x2 = guess;
        var f1 = this.XNPV(x1, values);
        var f2 = this.XNPV(x2, values);

        for (var i = 0; i < 100; i++) {
            if ((f1 * f2) < 0.0) break;
            if (Math.abs(f1) < Math.abs(f2)) {
                f1 = this.XNPV(x1 += 1.6 * (x1 - x2), values);
            }
            else {
                f2 = this.XNPV(x2 += 1.6 * (x2 - x1), values);
            }
        };

        if ((f1 * f2) > 0.0) return null;

        var f = this.XNPV(x1, values);
        if (f < 0.0) {
            var rtb = x1;
            var dx = x2 - x1;
        }
        else {
            var rtb = x2;
            var dx = x1 - x2;
        };

        for (var i = 0; i < 100; i++) {
            dx *= 0.5;
            var x_mid = rtb + dx;
            var f_mid = this.XNPV(x_mid, values);
            if (f_mid <= 0.0) rtb = x_mid;
            if ((Math.abs(f_mid) < 1.0e-6) || (Math.abs(dx) < 1.0e-6)) return x_mid;
        };

        return null;
    }

};

Answer 1

You want to use XIRR, so let's look at its signature:

XIRR: function(values, guess)

What does it do with values?

var f1 = this.XNPV(x1, values);

So values must be whatever XNPV is expecting. It has this chunk at its core:

for (var key in values) {
    var tmp = values[key];
    var value = tmp.Flow;
    var date = new Date(tmp.Date);
    xnpv += value / Math.pow(1 + rate, this.DaysBetween(firstDate, date)/365);
};

So it is expecting values to be a dictionary (associative array), where the value part of the key-value pair has members .Flow and .Date. I assume .Flow is the cashflow, and from the DaysBetween method I can see that .Date is a javascript Date. The key is ignored, so it can be a numeric if we want. So let's make one of those:

var myInstrument = { 0: {Flow: 5, Date: new Date(2015, 7, 6)},
                     1: {Flow: 105, Date: new Date(2016, 7, 6)} };

(That is a dictionary (or array) declaration).

The other input to XIRR is guess, which it will use to solve something, but it will default to using 0.1 (10% !) if given a falsy input value. So we can call it with our myInstrument thus:

var myInternalReturn = XIRR(myInstrument, false);

NB: The XIRR function is implementing the Actual/365 Fixed day count convention for Annual payment frequency, which may not be appropriate for the instrument you are valuing. Maybe it won't make much difference, but it will be incorrect for semi-annual or 30/360, with particular problems around month-end dates, simple interest instruments and so on