I need to be able to calculate the annual interest rate in the same way EXCEL does it in it's function called RATE However I cannot find the underlying math anywhere.
Preferably I would like to implement it in either ruby or python.
I need to be able to calculate the annual interest rate in the same way EXCEL does it in it's function called RATE However I cannot find the underlying math anywhere.
Preferably I would like to implement it in either ruby or python.
You can use the Exonio library in Ruby:
https://github.com/Noverde/exonio
Like this:
Exonio.rate(number_of_periods, payment_per_period, present_value)
numpy.rate()
computes the rate of interest per period.
rate(nper, pmt, pv, fv[, when, guess, tol, ...])
Here is a very simple example in VBA:
Function MyRATE(nper As Integer, pmt As Double, pv As Double, Optional fv As Double = 0, _
Optional PaymentEnd As Integer = 0, Optional guess As Double = 0.1)
Dim a As Double, b As Double, c As Double ' coefficients of the equation
Dim R As Double, RTmp As Double, i As Integer
' Initialize coefficients and R
R = 1 + guess
a = (pmt * (1 - PaymentEnd) - pv) / (pv + pmt * PaymentEnd)
b = (fv - pmt * PaymentEnd) / (pv + pmt * PaymentEnd)
c = (-pmt * (1 - PaymentEnd) - fv) / (pv + pmt * PaymentEnd)
' Iterate
For i = 1 To 20
RTmp = R - (R ^ (nper + 1) + a * R ^ nper + b * R + c) / ((nper + 1) * R ^ nper + a * nper * R ^ (nper - 1) + b)
If Abs(RTmp - R) < 0.0000001 Then Exit For
R = RTmp
Next i
If i <= 20 Then
MyRATE = RTmp - 1
Else
MyRATE = "N/A" ' Must try another guess
End If
End Function
To use the function (in a worksheet cell):
PLease note both the minus sign on the third argument and the multiplication by 12.