RATE FORMULA IN EXCEL

[dmkrish]

Hello,
I would like to know the formula used in excel for rate function. Anybody knows and can share with me ? as i need to use that in other programmining language where these financial functions are not available.

Thanks
dmkrish

 

[pgc01]

Hi dmkrish

There is no formula for the rate. If you check the help on PV() and solve the equation for rate you'll get a polynomial equation type:

x^(n+1) + a * x^n + b*x + c = 0

You may remember from school that a polynomial equation does not have a general algebraic solution if the degree is more than 4 (and even for 3 and 4 it involves some work).

To solve it you must use a numerical analyis method like bissection (basic) or Newton-Raphson (much better).

In conclusion you'll have to use a function to iterate and get an approximate result.

You can find on the web numerous examples of iteration methods and calculation of interest rate.

I just googled and found this one, that seems very clear:

http://www.voidware.com/tvm.htm

Hope this helps
PGC

 

[dmkrish]

Thank u PGC. But the site u referred does not help me much. I understand it should be solve by iteration but i would like to have some algorithm to incorprate in my tool. Well, my exact problem is : for a loan given i need to calculate "annualised percentage rate of charge" which have PV, series of payments (mly,qtly,hfly,yly)-PMT, & no.of years (nper).
If u know anyother site referring this topic with some algorithm please let me know.

Thanks & Regards
dmkrish

 

[sujittalukde]

Check this one
http://www.techonthenet.com/excel/formulas/rate.php

 

[dmkrish]

Thank u Sujith ; again it gives only explanation for the usage; i need algorithm(program)/logic behind this function to incorporate in our tool.

Thanks & Regards

 

[pgc01]

Hi again

I just did a first draft for a function equivalent to the worksheet function RATE(nper,pmt,pv,fv,type,guess).

I used the formula in the help:

pv*(1+rate)^nper+pmt*(1+rate*type)*(((1+rate)^nper-1)/rate)+fv=0

Wrote the equation in terms of R = 1 + r:

R ^ (nper+1) + a * R ^ nper + b * R + c = 0

So the iteration formula is (Newton-Raphson)

R(n+1) = R - (R ^ (nper+1) + a * R ^ nper + b * R + c) / ((nper+1)*R ^ nper + a * nper * R ^ (nper - 1) + b)

I tried some examples Rate / MyRate and it worked

=Rate(48, -200, 8000) (=0.77%, OK)
=Rate(10*1, -1000, 6500) (=8.71%, OK)
=Rate(7,0,-2000,3000) (=5.96%, OK)

Can you do some tests on this first version?

Kind regards
PGC

' Equivalent to the worksheet function RATE(nper,pmt,pv,fv,type,guess)
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

 

[dmkrish]

Hello PGC
Thank u for the reply. I have some questions: what is PaymentEnd stand for ? and i , the iteration taken for 20 , fixed value ?

Thanks & Regards
dmkrish

 

[pgc01]

Hi dmkrish

As I said the code is equivalent to the worksheet function Rate().

Please check the help on
RATE(nper,pmt,pv,fv,type,guess)

PaymentEnd is the parameter Type. I would not use the name Type for a variable because it's a reserved word.

As you see in the first paragraph of the help, excel uses 20 iterations and convergence within 0.0000001. I used the same. You can use any other values if you want. Please check the Newton-Raphson method, stability and speed of convergence, in mathematics books or web (just google "newton raphson convergence" for example).

If you have not the help installed (hope you have) you can get the help from rate here (check also the help on PV() for details):

http://office.microsoft.com/en-us/excel/HP052092321033.aspx?pid=CH062528251033

If you find examples where the code does not behave like the worksheet function Rate() please let me know.

Hope this helps
PGC

 

[dmkrish]

Hello PGC
For the examples u have mentioned i am not getting same result as urs. For myrate,R,Rtmp,a,b,c i have following values:
0,1,1,-1.0250,0,0.0250
0,1,1,-1.1538,0,0.1538
0,1,1,-1.1667,0,0.1667

The script is changed as follows for testing; i don't understand why there is difference...

Thanks & Regards
dmkrish
R = 1 + 0
pmt=-2000
pv=3000
nper=7
fv=0
i=1
a = (pmt * (1 - 0) - pv) / (pv + pmt * 0)
b = (fv - pmt * 0) / (pv + pmt * 0)
c = (-pmt * (1 - 0) - fv) / (pv + pmt * 0)

*Iterate
do while .T.
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
R = RTmp
exit
endif
enddo

If i <= 20 Then
MyRATE = RTmp - 1
Else
MyRATE = ["N/A" ' Must try another guess]
Endif
?myrate,r,rtmp,a,b,c

 

[pgc01]

Hi again dmkrish

In your example you are using

nper=7
pmt=-2000
pv=3000
fv=0

This means that you borrowed 3000 and are going to do 7 payments of 2000 to pay it. Which means you borrowed 3000 and will repay 14000!!! (you borrowed from a loan shark and you're afraid he'll break your legs ). I'm sure this is not what you mean or else you must try another (higher) guess.

I tried it with both Rate() and MyRate() and the result did not converge.

In this case the default guess of 10% is too low. Trying with a guess of 50% both formulas converge to a huge 65% per period.


If you try it with more plausible values, like period payments of 550 or 600 they converge to the same, reasonable, values. In this case you'd pay a total of 3850 or 4200 for a loan of 3000 over 7 periods. This gives interest rates of 6.66% and 9.20% which are normal if the period is a year, depend on the conditions of the specific loan.

I post these scenarios:

In B12 =RATE(B5,B6,B7,B8,B9,B10)
In B13 =MYRATE(B5,B6,B7,B8,B9,B10)

If you find other errors please always post the values of the rate parameters as they are defined in the help.

Book1
	A	B	C	D	E
1	Function:RATE(nper,pmt,pv,fv,type,guess)
2
3
4
5	nper	7	7	7
6	pmt	-2000	-550	-600
7	pv	3000	3000	3000
8	fv	0	0	0
9	Type
10	Guess	50%	10%	10%
11
12	Rate	64.63%	6.66%	9.20%
13	Myrate	64.63%	6.66%	9.20%
14
15
16	Totalpayment	-14000	-3850	-4200
17
Sheet2

I noticed that for your original scenario Excel's function errors out with the error #NUM whilst my code converged to 0. Although both clearly indicate an error, I'll try to change the code for these cases and also return an error.

Hope this helps
PGC