I am looking Ecel Rate() function in javascript or Algorithm, can someone give me. This is really urgent. Thanks.
Best Regards,
Wee Siong
Microsoft Office
Last Comment
Dave
8/22/2022 - Mon
TigerMan
Please restate your question more clearly weesiong.
Dave
weesiong
ASKER
Hi TigerMan,
In the Excel have a build in function call RATE(), i need the algorithm for it. Thanks.
Best Regards,
Wee Siong
Dave
The equation looks messay but is straightforward when you work through it
present value *(1+ rate)^num payments * paymount amount *(1+rate*type)+((1+rate)^num payments-1)/rate + future value = 0
present value = value of loan or investment right now
num payments = number of loan or investment payments (same amount paid each period )
rate = to be calculated
type = 0 (if first payment is at the end of period 1), 1 if first payment is at time 0
Future value = value outstanding at end of time
So if you took out an $10,000 loan now and paid $2000 off for 10 years and still owed $5000 at the end of 10 years then
I have found the function, but really thank for your help.
This is the function:
//Loan interest rate: Bisection algorithm
function loanrate(form,P,A,N) { with (Math) {
//Allow rates of 0 to 100%, solve to the nearest 0.001%
var a=0.000001;
var b=1;
var tol=0.000001
var test;
var t;
var y1;
var y2;
form.i.value = "did not converge";
var root;
var TEMP = a;
// Allow only 100 iterations (more than enough for convergence)
for(var k = 1; k<=100; k++) {
// A*(pow((1+i),N)-1)/(i*pow((1+i),N)) = present worth of N payments of A (beginning
// 1 period from now)
// Finally, subtract P (the present worth, or the price).
i = a;
y1 = A*(pow((1+i),N)-1)/(i*pow((1+i),N))-P;
i = b;
y2 = A*(pow((1+i),N)-1)/(i*pow((1+i),N))-P;
test = y1*y2;
// If test>0, the function that is to be driven to zero has the same sign
// for both sides of the interval.
if (test>0) {
b = a;
a = TEMP;
a = (a+b)/2;
} else {
// If test <0, the root of the equation is between a and b.
// If the interval is small enough, return (a+b)/2 as the root.
// If not, bisect the interval: save a as TEMP, move a (new) halfway toward b.
t = (b-a)/2;
if (t<tol) {
root = (a+b)/2;
// Compute the annual compounded rate, if payments are monthly
var annual = 100*(pow((1+root),12)-1);
root = 100*root;
form.i.value = round(1000*root)/1000;
form.i1.value = round(1000*annual)/1000;
break;
} else {
TEMP = a;
a = (a+b)/2;
}}}
}}
Best Regards,
Wee Siong
Dave
Thanks for the points and grade. :)
It was good that you posted your java solution for completeness, I'll give it a try sometime
Dave