asked on # Looking Excel RATE() in javascript or Algorithm

Hi All,

I am looking Ecel Rate() function in javascript or Algorithm, can someone give me. This is really urgent. Thanks.

Best Regards,

Wee Siong

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

Hi TigerMan,

In the Excel have a build in function call RATE(), i need the algorithm for it. Thanks.

Best Regards,

Wee Siong

In the Excel have a build in function call RATE(), i need the algorithm for it. Thanks.

Best Regards,

Wee Siong

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

10000*(1+rate)^10 - 2000*(1+rate * 0)*((1+rate)^10-1)/rate + 5000 = 0

goalseek rate to 11.05%

which is what RATE(10,-2000,10000,5000) does

Excel solves it by iteration

Cheers

Dave

present value *(1+ rate)^num payments * paymount amount *(1+rate*type)+((1+rate)^n

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

10000*(1+rate)^10 - 2000*(1+rate * 0)*((1+rate)^10-1)/rate + 5000 = 0

goalseek rate to 11.05%

which is what RATE(10,-2000,10000,5000) does

Excel solves it by iteration

Cheers

Dave

Hello Weesiong,

you could check

http://www.aemsinc.com/fincalc/index.html

http://www.aemsinc.com/fincalc/fincalc-how.html

HAGD:O)Bruintje

you could check

http://www.aemsinc.com/fincalc/index.html

http://www.aemsinc.com/fincalc/fincalc-how.html

HAGD:O)Bruintje

Hi brettdj and bruintje,

Thanks for reply, i am thinking cover RATE() to the javascript.

For the brettdj formula to give, i will try cover it to javascript.

For the bruintje site give, it is nice, but i no sure it is RATE() or not, i try input something and got the period too long error.

bruintje can you help me? I need a function Rate() in javascript, please help me..... thanks.

Best Regards,

Wee Siong

Thanks for reply, i am thinking cover RATE() to the javascript.

For the brettdj formula to give, i will try cover it to javascript.

For the bruintje site give, it is nice, but i no sure it is RATE() or not, i try input something and got the period too long error.

bruintje can you help me? I need a function Rate() in javascript, please help me..... thanks.

Best Regards,

Wee Siong

This is an IRR Function in web base.

function IRRCalc(CArray, guest) {

if (!guest) {

guest = 0.00;

}

inc =0.00001;

do

{

guest += inc;

NPV = 0;

for (var j=0; j<CArray.length; j++)

{

NPV += CArray[j]/Math.pow((1+guest),j);

}

}while(NPV > 0);

return guest * 100;

}

But now the client want change IRR to RATE.

Please help....

function IRRCalc(CArray, guest) {

if (!guest) {

guest = 0.00;

}

inc =0.00001;

do

{

guest += inc;

NPV = 0;

for (var j=0; j<CArray.length; j++)

{

NPV += CArray[j]/Math.pow((1+gues

}

}while(NPV > 0);

return guest * 100;

}

But now the client want change IRR to RATE.

Please help....

Hi brettdj,

For the site i testing again, but i donno which field is for which value. Can you give me a sample, let say

Nper = 10 (years)

PMT = 2000

PV = 10000

FV = 5000

Type = 0

Guess = 0.00001

How can i input in the webpage, really thank you so much. :)

Best Regards,

Wee Siong

For the site i testing again, but i donno which field is for which value. Can you give me a sample, let say

Nper = 10 (years)

PMT = 2000

PV = 10000

FV = 5000

Type = 0

Guess = 0.00001

How can i input in the webpage, really thank you so much. :)

Best Regards,

Wee Siong

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Hi brettdj,

Thanks for reply, i will try to make it myself. I will give you point later. Thanks.......

Best Regards,

Wee Siong

Thanks for reply, i will try to make it myself. I will give you point later. Thanks.......

Best Regards,

Wee Siong

Hi brettdj,

Thanks for reply, i will try to make it myself. I will give you point later. Thanks.......

Best Regards,

Wee Siong

Thanks for reply, i will try to make it myself. I will give you point later. Thanks.......

Best Regards,

Wee Siong

Hi All,

I found 1 page in http://www.ganesha.org/invest/fincalc.html#loan i no sure is it correct. Can help me to check, thanks.

Best Regards,

Wee Siong

I found 1 page in http://www.ganesha.org/invest/fincalc.html#loan i no sure is it correct. Can help me to check, thanks.

Best Regards,

Wee Siong

brettdj,

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

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 period from now)

// Finally, subtract P (the present worth, or the price).

i = a;

y1 = A*(pow((1+i),N)-1)/(i*pow(

i = b;

y2 = A*(pow((1+i),N)-1)/(i*pow(

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

Thanks for the points and grade. :)

It was good that you posted your java solution for completeness, I'll give it a try sometime

Cheers

Dave

It was good that you posted your java solution for completeness, I'll give it a try sometime

Cheers

Dave

Dave