|
Syntax
|
Rate
(NPer,Pmt,Pv,Fv,Due,Guess)
|
|
Description
|
Returns the rate of interest for each period of an annuity.
|
|
Comments
|
An annuity is a series of fixed payments made to an insurance company or other investment company over a period of time. Examples of annuities are mortgages and monthly savings plans.
The
Rate
function requires the following parameters:
|
|
|
Parameter
|
Description
|
|
|
NPer
|
Double representing the total number of payments in the annuity.
|
|
|
Pmt
|
Double representing the amount of each payment per period.
|
|
|
Pv
|
Double representing the present value of your annuity. In a loan situation, the present value would be the amount of the loan.
|
|
|
Fv
|
Double representing the future value of the annuity after the last payment has been made. In the case of a loan, the future value would be zero.
|
|
|
Due
|
Integer specifying when the payments are due for each payment period. A
0
indicates payment at the end of each period, whereas a
1
indicates payment at the start of each period.
|
|
|
Guess
|
Double specifying a guess as to the value the
Rate
function will return. The most common guess is .1 (10 percent).
|
|
|
Positive numbers represent cash received, whereas negative values represent cash paid out.
The value of Rate is found by iteration. It starts with the value of Guess and cycles through the calculation adjusting Guess until the result is accurate within 0.00001 percent. After 20 tries, if a result cannot be found, Rate fails, and the user must pick a better guess.
|
|
Example
|
This example calculates the rate of interest necessary to save $8,000 by paying $200 each year for 48 years. The guess rate is 10%.
Sub Main()
r# = Rate(48,-200,8000,0,1,.1)
MsgBox "The rate required is: " & Format(r#,"Percent")
End Sub
|
|
See Also
|
IPmt (function); NPer (function); Pmt (function); PPmt (function).
|