Page 133 - Excel Workbook for Dummies

13_798452 ch08.qxp 3/13/06 7:47 PM Page 116
116 Part II: Using Formulas and Functions
When using financial functions, keep in mind that the fv, pv, or pmt arguments can be
positive or negative, depending on whether you’re receiving the money (as in the
case of an investment) or paying out the money (as in the case of a loan). Also keep in
mind that you want to express the rate argument in the same units as the nper argu-
ment. For example, if you make monthly payments on a loan and you express the
nper as the total number of monthly payments (as in 360 (30 x 12) for 30-year mort-
gage), you need to express the annual interest rate in monthly terms as well. So if, for
example, you pay an annual interest rate of 7.5% on the loan, you express the rate
argument as 0.075/12 so that it is monthly as well.

Using the Basic Investment Functions

The following six functions form the core Excel financial functions for determining
whether a particular investment is worthwhile:
FV(rate,nper,pmt,[pv],[type]) — Future Value — calculates the future value of an
investment, assuming a constant interest rate and constant payments on a peri-
odic basis.
PV(rate,nper,pmt,[fv],[type]) — Present Value — calculates the present value of
an investment; that is, the total amount that a series of future payments is worth
now.
PMT(rate,nper,pv,[fv],[type]) — Payment — calculates the payment for a loan,
assuming a constant interest rate and stream of payments.

RATE(nper,pmt,pv,[fv],[type]) calculates the interest rate for any type of
annuity.
NPER(rate,pmt,pv,[fv],[type]) — Number of Periods — calculates the how many
periods are required for an investment, assuming a constant interest rate and
constant payments on a periodic basis.
NPV(rate,value1,[value2],[...]) — Net Present Value — calculates the present
value of an investment by using a discount rate and a series of future payments
(negative values) and income (positive values).

As you can see in this list, the syntax of these functions is similar, requiring, with the
exception of NPV, its own particular combination of the standard rate, nper, pmt, and
pv arguments discussed in the previous section. You will also note that the functions
that use these combination take the following optional arguments (indicated in the
square brackets):

[Pv] optional argument in the FV function is the present value or lump-sum
amount for which you wish to calculate the future value: If you omit the pv
argument, Excel assumes a present value of zero (0).
[Fv] optional argument in the PV, PMT, RATE, and NPER functions represents the
future value or cash balance you want to have after making your last payment: If
you omit the fv argument, Excel assumes a future value of zero (0).
[Type] optional argument in the FV, PV, PMT, RATE, and NPER functions indicates
whether the payment is made at the beginning or the end of the period — enter 0
(or omit the type argument) when the payment is made at the end of the period
and use 1 when it is made at the beginning of the period.

In the following exercise, you get a chance to practice using the FV, PV, PMT, RATE,
and NPER financial functions to solve a series of seven real-world problems found on
the four worksheets (FV, PV, PMT, and NPER) in the Exercise8-1.xls workbook file.