RATE function source code

  • Thread starter Thread starter straightdown12
  • Start date Start date
S

straightdown12

Hi,
I need to mimic what the RATE function in Excel does behind the scenes.
I know what to enter into the RATE function. Basically, I am writing a
program in another programming language that needs to find an interest
rate when given the inputs that are used for the RATE function. Does
anyone have any idea if it is possible to see the real equation for the
RATE function or if you know what it is can you post it please?
Thanks.
 
Help for PV gives the equation relating all the terms. Any term except
for rate can be solved explicitly. The solution for rate is iterative
(probably Newton's method).

Jerry
 
Thanks for the reply Jerry. I just wanted to confirm that the RATE
function is the same as the Newton-Raphson approximation. If the RATE
function uses something else I wanted to see what that formula was.
 
The Rate function is not the SAME as the Newton-Raphson approximation.

What JWL was saying is that you could USE the N-R method to solve the
non-explicit expression that contains Rate to converge on a value for rate.

The N-R method is an iterative one, where you start with a guess x0, and
then successive iterates are given by:

x sub n+1 = x sub n - f( x sub n ) / f '( x sub n )

where f ' is the derivative of f.

If you need details of how to use this, please just ask.

Regards

David
 
Thanks for the reply David. Do you know what formula the RATE function
uses since it is not Newton-Raphson? I have been trying to use
Newton-Raphson and have been getting very inconsistent results. The
RATE function seems more consistent to me.
 
Rate in XL is an iteratively calculated value.

Financial functions use the general equation:

PV+(PMT*(1-(1+RATE)^-NPer)/RATE)*(1+RATE)^TYPE+FV(1+RATE)^-NPer = 0

Which can be explicitly solved for PV, PMT, TYPE, NPer, or FV. RATE
uses iteration to drive the left side of the equation to 0.

butIn article <[email protected]>,
 
Newton-Raphson is search algorithm, not a formula. A simpler, if slower
search algorithm to start with would be bisection.

Rate = 0% would produce a payment that is too small.
Rate = 100% would hopefully produce a payment that is too large (unless
you are a loan shark)

L = 0%
U = 100%
try
Rate=(L+U)/2
if the resulting payment is too large, then replace U with Rate,
otherwise replace L with Rate.
Repeat the above process until U and L are sufficiently close together
for your purposes. The end result is not an exact solution, but one
that is numerically near the exact solution.


The Newton-Raphson algoithm is similar in concept, but uses a different
algorithm (given in David's note) to compute the next guess.

Jerry
 
Hi,
May be too late, but using NR algorithm to find i is very easy if we
have the derivative of the function.
In the case of rate:

Solution by Newton-Raphson method:

Given: n, PV, PMT
1-Define the values of: n, PV, PMT
2-Guess an initial value of i: i = 0.0 ; inovo=0.01
3-Execute while | inovo – i | > 0.00000001
3.1-Update i: i = inovo
3.2-Calculate the value of function and its derivative:
f = (1 - ((1 + i) ^ (-n))) * PMT / i - PV
df = PMT * (n * ((1 + i) ^ (-n - 1)) / i - (1 – ((1 + i) ^ (-n))) / (i
* i))
3.3-Calculate new i: inovo = i - (f / df)

Given: n, FV, PMT

3.2-Calculate the value of function and its derivative:
f = PMT/i * ( (1 + i)^n) - 1 ) - FV;
df = PMT/i * ( ( (1 + vI)^n) - 1 )/i + (n * (1 + i)^(n
- 1) ) ) )

I implemented these 2 algorithm in basic and delphi and the results
were nearly equal to the obtained with HP 12C calculator.
 
Back
Top