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