Hi,

Anyone know the formula for rate function? I should use it in a code
that is programmed in PHP. PHP doesn't have a ready-made RATE
function, so I should regenerate it somehow.

Ideas, suggestions??


--
maaritkk
------------------------------------------------------------------------
maaritkk's Profile: http://www.msusenet.com/member.php?userid=2096
View this thread: http://www.msusenet.com/t-1870523275

Rate uses a trial and error method. This is a rough approximation of the
function. You need to use both. Reproduce them in php.


Function myrate(nper, pmt, pv)

lowValue = 0
hiValue = 1
c = 0.5

comp = myPv(nper, pmt, c)

Do While (Abs(pv - comp) 0.001)
c = (lowValue + hiValue) / 2
comp = myPv(nper, pmt, c)
If (pv comp) Then
hiValue = c
Else
lowValue = c
End If
i = i + 1
If (i 100) Then
Exit Function
End If
Loop
myrate = c
End Function

Function myPv(nper, pmt, myrate)

myPv = pmt * (1 - (1 + myrate) ^ -nper) / myrate

End Function


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=377730

You are correct that there is no closed form for the RATE function, but
the finding of an approximate numerical solution is much more directed
than my interpretation of "trial and error". The formula to be solved
for rate is documented in Help for the PV function. While bisection
(used by your myrate function) is a serviceable approach to
approximating rate, I suspect that Excel's RATE function uses Newton's
method, which would converge much faster.

Jerry

mangesh_yadav wrote:

Rate uses a trial and error method. This is a rough approximation of the
function. You need to use both. Reproduce them in php.


Function myrate(nper, pmt, pv)

lowValue = 0
hiValue = 1
c = 0.5

comp = myPv(nper, pmt, c)

Do While (Abs(pv - comp) 0.001)
c = (lowValue + hiValue) / 2
comp = myPv(nper, pmt, c)
If (pv comp) Then
hiValue = c
Else
lowValue = c
End If
i = i + 1
If (i 100) Then
Exit Function
End If
Loop
myrate = c
End Function

Function myPv(nper, pmt, myrate)

myPv = pmt * (1 - (1 + myrate) ^ -nper) / myrate

End Function


Mangesh

Hi Jerry,

I didn't find a direct formula for rate in the PV function. Thats the actual
formula, which would need a goal seek.

As for the method I suggested, I agree its one of the clumsiest, and the
RATE function by excel would use one of the more efficient methods like
Newton's, but you see that the bisection is often the easiest to code off
hand.

Mangesh



"Jerry W. Lewis" wrote in message
...
You are correct that there is no closed form for the RATE function, but
the finding of an approximate numerical solution is much more directed
than my interpretation of "trial and error". The formula to be solved
for rate is documented in Help for the PV function. While bisection
(used by your myrate function) is a serviceable approach to
approximating rate, I suspect that Excel's RATE function uses Newton's
method, which would converge much faster.

Jerry

mangesh_yadav wrote:

Rate uses a trial and error method. This is a rough approximation of the
function. You need to use both. Reproduce them in php.


Function myrate(nper, pmt, pv)

lowValue = 0
hiValue = 1
c = 0.5

comp = myPv(nper, pmt, c)

Do While (Abs(pv - comp) 0.001)
c = (lowValue + hiValue) / 2
comp = myPv(nper, pmt, c)
If (pv comp) Then
hiValue = c
Else
lowValue = c
End If
i = i + 1
If (i 100) Then
Exit Function
End If
Loop
myrate = c
End Function

Function myPv(nper, pmt, myrate)

myPv = pmt * (1 - (1 + myrate) ^ -nper) / myrate

End Function


Mangesh

Hi,

The formula was very close to the results of rate function results, so
I used it. Anyways the calculations we are making are only
approximations so it is sufficient for our purposes. Thanks for the
help! :)


--
maaritkk
------------------------------------------------------------------------
maaritkk's Profile: http://www.msusenet.com/member.php?userid=2096
View this thread: http://www.msusenet.com/t-1870523275