Skip to content

Instantly share code, notes, and snippets.

@kucukharf
Last active February 23, 2024 08:42
Show Gist options
  • Save kucukharf/677d8f21660efaa33f72 to your computer and use it in GitHub Desktop.
Save kucukharf/677d8f21660efaa33f72 to your computer and use it in GitHub Desktop.
Excel RATE() Javascript Function
/*!
* @fileOverview Finance Excel Rate Formula Javascript Equivalent
* @version 1.0.0
*
* @author Burak Arslan @kucukharf http://www.github.com/kucukharf
* @license
* Copyright (c) 2010-2018 Burak Arslan
* Licensed under Creative Commons (CC) license
* @usage RATE($periods, $payment, $present, $future, $type, $guess)
*/
RATE = function(periods, payment, present, future, type, guess) {
guess = (guess === undefined) ? 0.01 : guess;
future = (future === undefined) ? 0 : future;
type = (type === undefined) ? 0 : type;
// Set maximum epsilon for end of iteration
var epsMax = 1e-10;
// Set maximum number of iterations
var iterMax = 10;
// Implement Newton's method
var y, y0, y1, x0, x1 = 0,
f = 0,
i = 0;
var rate = guess;
if (Math.abs(rate) < epsMax) {
y = present * (1 + periods * rate) + payment * (1 + rate * type) * periods + future;
} else {
f = Math.exp(periods * Math.log(1 + rate));
y = present * f + payment * (1 / rate + type) * (f - 1) + future;
}
y0 = present + payment * periods + future;
y1 = present * f + payment * (1 / rate + type) * (f - 1) + future;
i = x0 = 0;
x1 = rate;
while ((Math.abs(y0 - y1) > epsMax) && (i < iterMax)) {
rate = (y1 * x0 - y0 * x1) / (y1 - y0);
x0 = x1;
x1 = rate;
if (Math.abs(rate) < epsMax) {
y = present * (1 + periods * rate) + payment * (1 + rate * type) * periods + future;
} else {
f = Math.exp(periods * Math.log(1 + rate));
y = present * f + payment * (1 / rate + type) * (f - 1) + future;
}
y0 = y1;
y1 = y;
++i;
}
return rate;
};
@nurdiansyah
Copy link

thanks

@RioChndr
Copy link

You really save my day.

@Tforit
Copy link

Tforit commented Jan 31, 2024

hello
periods = 24
payment = -12600
present = 68310

gives -1.4630839294992086
on excel gives 0.1810537

any help

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment