Looking Excel RATE() in javascript or Algorithm

Please restate your question more clearly weesiong.

Dave

Hi TigerMan,

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

Hello Weesiong,

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

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....

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

Hi brettdj,

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

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

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

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