Avatar of weesiong
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
Microsoft Office

Avatar of undefined
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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Brian Mulder

weesiong

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
weesiong

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+guest),j);
          }
     }while(NPV > 0);
     return guest * 100;
}

But now the client want change IRR to RATE.

Please help....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
weesiong

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
ASKER CERTIFIED SOLUTION
Dave

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
weesiong

ASKER
Hi brettdj,

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

Best Regards,
Wee Siong
weesiong

ASKER
Hi brettdj,

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

Best Regards,
Wee Siong
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
weesiong

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
weesiong

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

Cheers

Dave
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.