Instantly share code, notes, and snippets.

# pies/ExcelFormulas.js Created Nov 29, 2012

Few Excel formulas - PMT, PPMT, XIRR - expressed in Javascript
 /* Based on * - EGM Mathematical Finance class by Enrique Garcia M. * - 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 commented Sep 10, 2013

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

### kukat 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 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 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 commented Jul 30, 2017

 Saved me, thanks!

 thanks