Microsoft Office Tutorials and References

In Depth Information

The syntax of the RATE function is similar to those of the PMT family of functions.

RATE(
nper
pmt
type
guess
)
,

Because there are some differences between RATE and the other PMT functions, Table 9-4

summarizes the RATE function’s arguments for you.

Table 9-4.
The Rate Function’s Arguments

Argument

Description

Nper

The total number of payment periods in an annuity.

Pmt

The payment made each period. This value can’t change over the life of

the annuity. If
pmt
is omitted, you must include the
fv argument.

Pv

The present value of the annuity—the total amount that a series of future

payments is worth now.

Fv

The future value, or a cash balance that you want to attain after the last

payment is made. If
fv is omitted, it’s assumed to be 0. (The future value

of a loan, for example, is 0.)

Type

The number 0 or 1, indicating when payments are due. (0 is the default,

which means payments are due at the end of the month.)

Guess

Your guess for what the rate will be. If you omit guess, it’s assumed to be

10 percent. If RATE doesn’t converge, try different values for
Guess
. RATE

usually converges if
Guess
is between 0 and 1.

So, if you wanted to figure out the interest rate on a $150,000 home loan that you were paying

back at $1,186.19 a month over 15 years, you would use the following formula to determine

the annual percentage rate of the loan:

=RATE(180,-1186.19,150000)*12

It’s important to enter the payment (the second parameter) as a negative number. It might

make it easier to remember this requirement if you think of the payment as money that’s

leaving your bank account.

Determining the Present Value of an Investment

The PV, or
present value
, function returns the present value of an investment, which is

finance-speak for the total amount of money that a series of equal-value future payments is

worth now. When you borrow money, the loan amount is the present value to the lender.

For example, if you wanted to calculate the present value of a $150,000 loan with 5 percent

interest to be paid back monthly over 15 years, you would use the following formula:

PV(
rate
nper
pmt
type
)
fv,