Page 132 - Excel Workbook for Dummies

13_798452 ch08.qxp 3/13/06 7:47 PM Page 115

Chapter 8

Financial Formulas

and Functions

In This Chapter
Understanding how the basic financial functions work
Using the basic investment accumulation, discounting, and amortization functions
Using the program’s depreciation functions

s the old song says, “Money makes the world go ’round” and to that end, Excel sup-
Aplies you with plenty of built-in functions for getting the latest spin on how much
you’re making and spending. In this chapter, you get the chance to practice using some of
the basic investment, depreciation, and currency-conversion functions that make up a part
of the program’s financial group.

Working with Financial Functions

Excel offers a fair number of sophisticated financial functions for determining such things as
the present, future, or net present value of an investment; the payment, number of periods,
or the principal or interest part of a payment on an amortized loan; the rate of return on an
investment; and the depreciation of your favorite assets.

By activating the Analysis ToolPak add-in, you add some 30-odd specialized financial func-
tions that run the gamut from those that calculate the accrued interest for a security paying
interest periodically and only at maturity all the way to those that calculate the internal rate
of return and the net present value for a schedule of non-periodic cash flows.

The key to using many of Excel’s financial functions is in understanding the terminology used
by their arguments. Many of the most common financial functions such as PV (Present Value),
NPV (Net Present Value), FV (Future Value), and PMT (Payment) take similar arguments:

Pv is the present value that is the principal amount of the annuity
Fv is the future value that represents the principal plus interest on the annuity
Pmt is the payment made each period in the annuity. Normally, the payment is set over
the life of the annuity and includes principal plus interest without any other fees.
Rate is the interest rate per period. Normally, the rate is expressed as an annual
percentage.
Nper is the total number of payment periods in the life of the annuity. It is calculated
by taking the Term (the amount of time that interest is paid) and multiplying it by the
Period (the point in time when interest is paid or earned) so that a loan with a 3-year
term with 12 monthly interest payments has 3 x 12, or 36, payment periods.