weesiong
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
ASKER
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)^n um 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
ASKER
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
ASKER
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+gues t),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....
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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
ASKER
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
ASKER
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