Page 237 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

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

RATE(nper,pmt,pv,fv,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, Chapter 9
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,fv,type)

211
Part 4 Advanced VBA