Page 188 - Excel Progamming Weekend Crash Course

k540629 ch13.qxd 9/2/03 9:34 AM Page 163

Session 13 — Programming with Excel’s Built-In Functions 163

Excel Function Overview

The remainder of this session provides information on some of the more frequently used
functions, along with some examples. Excel divides its functions into categories, and this
session follows the same arrangement. Not all categories are included. Some of the more
specialized, such as statistical functions, are beyond the scope of this book.
Logical functions are covered in Session 12, and database functions are cov-
ered in Session 27.
Cross-Ref

Financial Functions
Excel’s financial functions can perform a variety of sophisticated calculations, including
annuities, depreciation, and so on. The function that is perhaps used most often is PMT,
which calculates the payments on a loan. For example, if you were thinking about taking
out a car loan of $20,000 at 5% for four years, this function could tell you what your
monthly payments would be.
The syntax for the PMT function is:

PMT(rate, nper, pv, fv, type)

Rate. The interest rate being charged, per period.
Nper. The number of periods, or term, of the loan.
Pv. The amount of the loan (present value).
Fv. Optional. The amount owed at the end of the payments (future value). For most
loans this is 0, which is the default value if this argument is omitted.
Type. Optional. Set to 0 (the default) if payments are due at the end of each period.
Set to 1 if the payments are due at the beginning of each period.
The return value of the function is the payment due per period. It is essential that the
period used for the rate argument and the nper argument are the same. Most loans quote
an annual interest rate but require payments monthly. Continuing with the example given
previously, you would use months and would have:

5% interest per year is the same as 0.05/12 per month.
Four-year loan period is the same as 4*12 months.

Real-world monthly payments may be different from the results returned by
the PMT function because of extra fees tacked onto the loan payment.
Note
The formula to calculate the monthly payment on this loan would be:

=PMT(0.05/12, 4*12, 20000)