Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Few Excel formulas - PMT, PPMT, XIRR - expressed in Javascript
/* 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;
}
};
@cintrzyk

This comment has been minimized.

Copy link

commented Sep 10, 2013

Hi, can you write some example how to use function XIRR?

@kukat

This comment has been minimized.

Copy link

commented Jul 8, 2014

    FV: function(rate, nper, pmt, pv, type) {
        if (!type) type = 0;

        var pow = Math.pow(1 + rate, nper);
        var fv = 0;

        if (rate) {
            fv = (pmt * (1 + rate * type) * (1 - pow) / rate) - pv * pow;
        } else {
            fv = -1 * (pv + pmt * nper);
        }

        return fv;
    },
@liorchaga

This comment has been minimized.

Copy link

commented Aug 13, 2014

Great library, thanks.
loop for calculating f1 and f2 should run only 10 itereation, not a hundred.
See http://plnkr.co/edit/xydnDufWpxsF8wJiOAq5?p=preview and compare calculations to excel

@shaunak-12345

This comment has been minimized.

Copy link

commented Jun 27, 2015

Here for Using XIRR functionality we have to pass array of objects ie; values array in the parameter.
It will be like this:
var values =
[
{Date:'1/24/2012',Flow: -10000},
{Date:'6/27/2015',Flow: 12000}
];

And then pass this values array in XIRR function like this:
ExcelFormulas.XIRR(values);

@mbenedettini

This comment has been minimized.

Copy link

commented Jul 30, 2017

Saved me, thanks!

@elshazer

This comment has been minimized.

Copy link

commented Mar 20, 2018

thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.