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).

R