M
mich
what is the mathematical formula of RATE(nper,pmt,pv,fv,type,guess); how can
we calculated on paper?
we calculated on paper?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
mich
hello bernie,
i would like to know if i have all the arguments except the rate, how can i
calculate it on paper
if
pmt= pv * i / 1 - (1/(1+i)^n)
where i=rate, n=period, pmt=annuity, pv=present value
i=?
i would like to know if i have all the arguments except the rate, how can i
calculate it on paper
if
pmt= pv * i / 1 - (1/(1+i)^n)
where i=rate, n=period, pmt=annuity, pv=present value
i=?
B
Bernie Deitrick
According to Help, it is an iterative calaculation: guess one value, calc, guess another, calc, and
based on the results of the first two, guess a third, and continue.... That's why we use Excel
rather than paper and pencil to do these sorts of things.
HTH,
Bernie
MS Excel MVP
based on the results of the first two, guess a third, and continue.... That's why we use Excel
rather than paper and pencil to do these sorts of things.
HTH,
Bernie
MS Excel MVP
M
mich
i totally agree with u that we would rather use excel, but i have to verify
on paper and pencil to a client the result of the calculation; It is math, if
i have the formula : pmt= pv * i / 1 - (1/(1+i)^n) so i = ? according to pmt,
pv, n
on paper and pencil to a client the result of the calculation; It is math, if
i have the formula : pmt= pv * i / 1 - (1/(1+i)^n) so i = ? according to pmt,
pv, n
J
joeu2004
mich said:i totally agree with u that we would rather use excel, but i have to verify
on paper and pencil to a client the result of the calculation; It is math, if
i have the formula : pmt= pv * i / 1 - (1/(1+i)^n) so i = ? according to pmt,
pv, n
As Bernie wrote, "i" can only be computed by iterating over succesive
estimates.
IMHO, the best way to verify "i" to satisfy a client would be to let
Excel (or a business calculator) compute "i", then plug that "i" into
your pmt formula (i.e. given pv, n and "i", compute pmt) and
demonstrate that that "i" correctly computes pmt (approximately).
Alternatively, perhaps it would be good enough to compute "i" with
another independent program -- e.g. an HP-12C calculator or the TI
equivalent.
D
Dana DeLouis
i have the formula : pmt= pv * i / 1 - (1/(1+i)^n)
The above equation may be what you need, so I'll just throw this out. I
believe the "basic" Pmt function is the following:
pmt = (pv*i *(i + 1)^n) / ((i + 1)^n - 1)
or
pmt = pv*i*(1 + 1/((i + 1)^n - 1))
The above equation may be what you need, so I'll just throw this out. I
believe the "basic" Pmt function is the following:
pmt = (pv*i *(i + 1)^n) / ((i + 1)^n - 1)
or
pmt = pv*i*(1 + 1/((i + 1)^n - 1))
T
Tushar Mehta
See http://www.aemsinc.com/fincalc/fincalc-how.html though I don't know howwhat is the mathematical formula of RATE(nper,pmt,pv,fv,type,guess); how can
we calculated on paper?
long that page will be available.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
D
Dana DeLouis
Out of educational curiosity, I believe this is the Newton equation for
Interest Rate (r)
Function Rate(n, pmt, pv)
Dim j As Long
Dim r, num, den
r = pmt / pv 'Best Guess
For j = 1 To 10
num = (pmt * (r + 1) * ((r + 1) ^ n - 1) ^ 2 - n * pv * r ^ 2 * (r +
1) ^ n)
den = ((r + 1) ^ n * (pv * (r + 1) ^ (n + 1) - pv * (n * r + r +
1)))
r = num / den
Debug.Print j; r
Next j
Rate = r
End Function
Interest Rate (r)
Function Rate(n, pmt, pv)
Dim j As Long
Dim r, num, den
r = pmt / pv 'Best Guess
For j = 1 To 10
num = (pmt * (r + 1) * ((r + 1) ^ n - 1) ^ 2 - n * pv * r ^ 2 * (r +
1) ^ n)
den = ((r + 1) ^ n * (pv * (r + 1) ^ (n + 1) - pv * (n * r + r +
1)))
r = num / den
Debug.Print j; r
Next j
Rate = r
End Function
T
Tushar Mehta
Hi Dana,
When I wrote that page, I started with the PV formula and used first
principles to come up with a linear iteration approach. Would be really
surprised if I were the first to do that. {grin}
But, interestingly enough, a cursory search of google (PV "interest rate"
calculate) wasn't very helpful. The "best" I found was someone bragging
about a having a PhD in Finance and having written "several calculators in
several languages" explain how complicated the process was. Seemed like
s/he was just trying to drum up business.
Some of the other pages addressing the subject conveniently ignored
payments.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
When I wrote that page, I started with the PV formula and used first
principles to come up with a linear iteration approach. Would be really
surprised if I were the first to do that. {grin}
But, interestingly enough, a cursory search of google (PV "interest rate"
calculate) wasn't very helpful. The "best" I found was someone bragging
about a having a PhD in Finance and having written "several calculators in
several languages" explain how complicated the process was. Seemed like
s/he was just trying to drum up business.
Some of the other pages addressing the subject conveniently ignored
payments.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
D
Dana DeLouis
Hi Tushar. I thought this was interesting also. I was just messing around,
and noticed that with the Newton version, (r+1) is listed 6 times. I
thought I'd factor it out with a variable instead (k=r+1).
I'm not an expert here, but I thought this observation was interesting. If
one calculates a payment (PMT) with a high interest rate (say 30%, 110%,
etc), one's payments are obviously high. If I use Excel's Rate function to
calculate this high interest rate, I usually get a #NUM! error unless I
supply a guess that is very close to the true interest rate.
It appears to me that as the interest rate increases, the rate limit
approaches Pmt/Pv. As the rate increases, the initial guess appears to get
better.
Most of my limited testing had a solution in about 5-6 loops. I was curious
on Excel's help on Rate where it states...
"...If the successive results of RATE do not converge to within 0.0000001
after 20 iterations, RATE returns the #NUM! error value."
I would be curious to learn what iteration technique Excel is using.
Again, this is not fully tested, but was done out of curiosity.
Function intRate(n, pmt, pv)
Dim j As Long
Dim r, k, num, den
Dim t
'// Check if payment is based on 0% Interest
If Abs(pmt - pv / n) < 0.01 Then
intRate = 0
Exit Function
End If
'// Check if payment is too low:
If pmt < pv / n Then
intRate = "Payment below " & FormatCurrency(pv / n, 2)
Exit Function
End If
'// Best Guess!
r = pmt / pv
For j = 1 To 10
k = r + 1
num = k * (k ^ n - 1) ^ 2 * pmt - k ^ n * n * pv * r ^ 2
den = k ^ n * (k ^ (n + 1) * pv - pv * (k + n * r))
r = num / den
If r <> t Then t = r Else Exit For
Next j
intRate = r
End Function
and noticed that with the Newton version, (r+1) is listed 6 times. I
thought I'd factor it out with a variable instead (k=r+1).
I'm not an expert here, but I thought this observation was interesting. If
one calculates a payment (PMT) with a high interest rate (say 30%, 110%,
etc), one's payments are obviously high. If I use Excel's Rate function to
calculate this high interest rate, I usually get a #NUM! error unless I
supply a guess that is very close to the true interest rate.
It appears to me that as the interest rate increases, the rate limit
approaches Pmt/Pv. As the rate increases, the initial guess appears to get
better.
Most of my limited testing had a solution in about 5-6 loops. I was curious
on Excel's help on Rate where it states...
"...If the successive results of RATE do not converge to within 0.0000001
after 20 iterations, RATE returns the #NUM! error value."
I would be curious to learn what iteration technique Excel is using.
Again, this is not fully tested, but was done out of curiosity.
Function intRate(n, pmt, pv)
Dim j As Long
Dim r, k, num, den
Dim t
'// Check if payment is based on 0% Interest
If Abs(pmt - pv / n) < 0.01 Then
intRate = 0
Exit Function
End If
'// Check if payment is too low:
If pmt < pv / n Then
intRate = "Payment below " & FormatCurrency(pv / n, 2)
Exit Function
End If
'// Best Guess!
r = pmt / pv
For j = 1 To 10
k = r + 1
num = k * (k ^ n - 1) ^ 2 * pmt - k ^ n * n * pv * r ^ 2
den = k ^ n * (k ^ (n + 1) * pv - pv * (k + n * r))
r = num / den
If r <> t Then t = r Else Exit For
Next j
intRate = r
End Function
M
mich
thanks tushar the site below was very helpfull
Tushar Mehta said:See http://www.aemsinc.com/fincalc/fincalc-how.html though I don't know how
long that page will be available.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
P
Pieter Vandenberg
Hello:
Sorry for the late follow-up but I was on vacation, but I think the
following might help someone.
The solution for a rate is a root finding problem. As a result the
problem can be difficult or impossible to solve if certain values
occur. Therefore the root finding techniques sometimes fail, or go on
forever. Here is the way to explain the problem.
Assume a two period problem then the present value of the flows equal:
PV = PMT/(1+r)^1 + PMT/(1+r)^2
This can be solved for r by re-arranging the terms:
PV - PMT/(1+r)^1 + PMT/(1+r)^2 = 0
rewriting and changing symbols and allowing for the possibility that
the PMT's are not the same (a = PV; b = PMT period 1; c = PMT period
2):
PV - PMT/(1+r)^1 + PMT/(1+r)^2 = a - b/(1+r)^1 + c/(1+r)^2
This would also work if b = c .
Then multiplying through by (1+r)^2:
a(1+r)^2 + b(1+r) + c = 0
now let (1+r) = x
ax^2 + bx + c = 0
This is quadratic formula which can be solved for x by the quadratic
formula:
x = (- b +/- sqrt(b^2 - 4ac))/2a
provided that (4ac) < (b^2), otherwise we cannot find an answer in the
real number domain. Also it is possible that more than one answer can
exist because of the (+/-) factor.
Now Descartes Rule of Signs tells us that there will one real root if
the sign change on the coefficients is one, if more than one we could
get a variety of roots ranging from the number of sign changes down to
zero. (e.g. -++, - - +, or ++- is one sign change; -+-, or +-+ is two
sign changes)So the "standard cash flows" are an outflow, followed by
inflows or an inflow followed by outflows. This would mean one sign
change and therefore one real root.
Suppose we have the following cash flows:
Per 1 -4
Per 2 +1
Per 3 +3
You can see by inspection that the rate of return is 0%, but it turns
out that there is another answer that mathematically cannot be
rejected, it is -175%. The solution using the quadratic formula:
-4 + 1/(1+r) + 3/(1+r)^2 = 0
Multiply through by (1+r)^2
-4*(1+r)^2 + 1*(1+r) + 3 = 0
Let x = (1+r)
-4*x^2 + 1x + 3 = 0
Solving using the quadratic:
{-1 +/- sqrt(1^2 - 4*-4*3)}/(2*-4)
gives us x = 1 and x = -.75
Or an r of 0% and an r of -175%
Now if you expand the number of periods you run into a problem. There
is a cubic formula for solving a 3 period problem and if you have a 4
period problem you know that is two quadratics and you could solve if
you can factor it into the two quadratics. Now expand to 5 periods and
you know that is a quadratic and a cubic, can you factor it? (If I
recall correctly someone a long time ago proved that there could not be
a general formula for a 5th degree equation.) And it just keeps going.
So mathematicians quickly determined that the best way to solve these
types of problems is to use numerical analysis, which is a way to guess
at the answer. Of course they quickly developed very good method of
making this guessing process very efficient. Newton-Rhapson or
bisection both give very quick answers if the problem is well behaved,
as it would be if you had constant payments as in a loan, which insures
that either the cash flows are - + + + + + + or from the borrowers
standpoint + - - - - - - - either one generally results in a quick
answer. Interestingly while something like Newton-Rhapson might do it
in fewer guesses than bisection but that does not mean that it is
faster. A long time ago on a mainframe I worked with both solution
algorithms and found that in terms of CPU time bisection was faster,
even though it took many more guesses.
But given the general problem of finding roots Descartes rules of signs
tells us it is possible that no solution exists, or that many solutions
exist. Both of these possibilities have to be addressed in a root
finding technique, which is what Excel's "rate" and "irr" are. (I
suspect that it uses the same root finder for both functions. In
general you don't want the root finder to hang. Change the cash flows
a little and you can see what happens.
With the following the rate is 0% and 25%, which is the correct one?
Per 1 -4
Per 2 +9
Per 3 -5
With the following there is no rate in the real number domain. The
function never crosses the abscissa, which is you might say the
definition of the root of an equation. This will drive a badly written
root finder into a loop.
Per 1 +4
Per 2 -9
Per 3 +5.5
So it is necessary to handle these situations. On the above problems
Excel returns #NUM for the latter and for the former the answer you get
depends on the guess rate you give it. Here are some results and the
guess rate:
Guess IRR or Rate
0% 0%
10% 0%
11% 0%
12% 25%
13% 25%
15% 25%
100% 25%
150% 0%
As you can see the guess rate does not always produce the result you
would think.
Finally it is very unlikely that the root(s) of the equation will turn
out to be even numbers. Therefore the routine to find the root could
run a very long time as it tries to drive the result to exactly zero.
Therefore there is usually a test to see if the answer is close enough
to some defined error allowance or it simply stops after some number of
estimates. Excel appears to use both techniques.
mich wrote:
Pieter Vandenberg
Sorry for the late follow-up but I was on vacation, but I think the
following might help someone.
The solution for a rate is a root finding problem. As a result the
problem can be difficult or impossible to solve if certain values
occur. Therefore the root finding techniques sometimes fail, or go on
forever. Here is the way to explain the problem.
Assume a two period problem then the present value of the flows equal:
PV = PMT/(1+r)^1 + PMT/(1+r)^2
This can be solved for r by re-arranging the terms:
PV - PMT/(1+r)^1 + PMT/(1+r)^2 = 0
rewriting and changing symbols and allowing for the possibility that
the PMT's are not the same (a = PV; b = PMT period 1; c = PMT period
2):
PV - PMT/(1+r)^1 + PMT/(1+r)^2 = a - b/(1+r)^1 + c/(1+r)^2
This would also work if b = c .
Then multiplying through by (1+r)^2:
a(1+r)^2 + b(1+r) + c = 0
now let (1+r) = x
ax^2 + bx + c = 0
This is quadratic formula which can be solved for x by the quadratic
formula:
x = (- b +/- sqrt(b^2 - 4ac))/2a
provided that (4ac) < (b^2), otherwise we cannot find an answer in the
real number domain. Also it is possible that more than one answer can
exist because of the (+/-) factor.
Now Descartes Rule of Signs tells us that there will one real root if
the sign change on the coefficients is one, if more than one we could
get a variety of roots ranging from the number of sign changes down to
zero. (e.g. -++, - - +, or ++- is one sign change; -+-, or +-+ is two
sign changes)So the "standard cash flows" are an outflow, followed by
inflows or an inflow followed by outflows. This would mean one sign
change and therefore one real root.
Suppose we have the following cash flows:
Per 1 -4
Per 2 +1
Per 3 +3
You can see by inspection that the rate of return is 0%, but it turns
out that there is another answer that mathematically cannot be
rejected, it is -175%. The solution using the quadratic formula:
-4 + 1/(1+r) + 3/(1+r)^2 = 0
Multiply through by (1+r)^2
-4*(1+r)^2 + 1*(1+r) + 3 = 0
Let x = (1+r)
-4*x^2 + 1x + 3 = 0
Solving using the quadratic:
{-1 +/- sqrt(1^2 - 4*-4*3)}/(2*-4)
gives us x = 1 and x = -.75
Or an r of 0% and an r of -175%
Now if you expand the number of periods you run into a problem. There
is a cubic formula for solving a 3 period problem and if you have a 4
period problem you know that is two quadratics and you could solve if
you can factor it into the two quadratics. Now expand to 5 periods and
you know that is a quadratic and a cubic, can you factor it? (If I
recall correctly someone a long time ago proved that there could not be
a general formula for a 5th degree equation.) And it just keeps going.
So mathematicians quickly determined that the best way to solve these
types of problems is to use numerical analysis, which is a way to guess
at the answer. Of course they quickly developed very good method of
making this guessing process very efficient. Newton-Rhapson or
bisection both give very quick answers if the problem is well behaved,
as it would be if you had constant payments as in a loan, which insures
that either the cash flows are - + + + + + + or from the borrowers
standpoint + - - - - - - - either one generally results in a quick
answer. Interestingly while something like Newton-Rhapson might do it
in fewer guesses than bisection but that does not mean that it is
faster. A long time ago on a mainframe I worked with both solution
algorithms and found that in terms of CPU time bisection was faster,
even though it took many more guesses.
But given the general problem of finding roots Descartes rules of signs
tells us it is possible that no solution exists, or that many solutions
exist. Both of these possibilities have to be addressed in a root
finding technique, which is what Excel's "rate" and "irr" are. (I
suspect that it uses the same root finder for both functions. In
general you don't want the root finder to hang. Change the cash flows
a little and you can see what happens.
With the following the rate is 0% and 25%, which is the correct one?
Per 1 -4
Per 2 +9
Per 3 -5
With the following there is no rate in the real number domain. The
function never crosses the abscissa, which is you might say the
definition of the root of an equation. This will drive a badly written
root finder into a loop.
Per 1 +4
Per 2 -9
Per 3 +5.5
So it is necessary to handle these situations. On the above problems
Excel returns #NUM for the latter and for the former the answer you get
depends on the guess rate you give it. Here are some results and the
guess rate:
Guess IRR or Rate
0% 0%
10% 0%
11% 0%
12% 25%
13% 25%
15% 25%
100% 25%
150% 0%
As you can see the guess rate does not always produce the result you
would think.
Finally it is very unlikely that the root(s) of the equation will turn
out to be even numbers. Therefore the routine to find the root could
run a very long time as it tries to drive the result to exactly zero.
Therefore there is usually a test to see if the answer is close enough
to some defined error allowance or it simply stops after some number of
estimates. Excel appears to use both techniques.
mich wrote:
Pieter Vandenberg
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.