Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Looking Excel RATE() in javascript or Algorithm

Posted on 2003-02-27
13
Medium Priority
?
3,021 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:weesiong
13 Comments
 
LVL 5

Expert Comment

by:TigerMan
ID: 8033155
Please restate your question more clearly weesiong.

Dave
0
 
LVL 7

Author Comment

by:weesiong
ID: 8033209
Hi TigerMan,

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

Best Regards,
Wee Siong
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8034462
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 44

Expert Comment

by:bruintje
ID: 8035135
0
 
LVL 7

Author Comment

by:weesiong
ID: 8037330
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
0
 
LVL 7

Author Comment

by:weesiong
ID: 8037348
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....
0
 
LVL 7

Author Comment

by:weesiong
ID: 8037514
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
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 8038721
Hi Wee Siong,

Sorry, I don't know anything about java.......

You should be able to solve the algorithim I gave you above by an iterative process - I'm assuming you can find such a method in java. Plug the numbers in to the variables and solve for rate so that the left hand side of the equation is 0.

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

which simplifies to

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

Good luck

Dave
0
 
LVL 7

Author Comment

by:weesiong
ID: 8038911
Hi brettdj,

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

Best Regards,
Wee Siong
0
 
LVL 7

Author Comment

by:weesiong
ID: 8039045
Hi brettdj,

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

Best Regards,
Wee Siong
0
 
LVL 7

Author Comment

by:weesiong
ID: 8042411
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
0
 
LVL 7

Author Comment

by:weesiong
ID: 8086314
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 8086337
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question