Bookshelf Home | Contents | Index | PDF

## Rate Function

This standard VB function returns the interest rate per period for an annuity or a loan.

##### Syntax

Rate(nper, pmt, pv, fv, due, guess)

Argument
Description

nper

The total number of payment periods

pmt

The constant periodic payment per period

pv

The present value of the initial lump sum amount paid (as with an annuity) or received (as with a loan)

fv

The future value of the final lump sum amount required (as with a savings plan) or paid (0 as with a loan)

due

0 if due at the end of each period

1 if due at the beginning of each period

guess

An estimate for the rate returned

##### Returns

The interest rate per period.

##### Usage

In general, a guess of between 0.1 (10 percent) and 0.15 (15 percent) would be a reasonable value for guess.

Rate is an iterative function: It improves the given value of guess over several iterations until the result is within 0.00001 percent. If it does not converge to a result within 20 iterations, it signals failure.

##### Example

This example finds the interest rate on a 10-year \$25,000 annuity that pays \$100 per month.

Sub Button_Click
Dim aprate
Dim periods
Dim payment, annuitypv
Dim annuityfv, due
Dim guess
Dim msgtext as String
periods = 120
payment = 100
annuitypv = 0
annuityfv = 25000
guess = .1
' Assume payments are made at end of month
due = 0
aprate = Rate(periods,-payment,annuitypv,annuityfv, _
due, guess)
aprate = (aprate * 12)
msgtext = "The percentage rate for a 10-year \$25,000 _
annuity"
msgtext = msgtext & "that pays \$100/month has "
msgtext = msgtext & "a rate of: " & Format(aprate, _
"Percent")
End Sub