?
Solved

Looking Excel RATE() in javascript or Algorithm

Posted on 2003-02-27
13
Medium Priority
?
2,869 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

770 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