Microsoft Office Tutorials and References

In Depth Information

**Chapter 31: Frequently Used Financial Functions**

CHAPTER
31

Frequently Used Financial Functions

There are a few Excel financial functions that look and feel alike. After you

understand and master one of these functions, you can navigate and utilize all of

them with relative ease. All these financial functions deal with a stream of equal

periodical payments called annuities. Annuities could include loans, mortgages, or

retirement funds; transactions where you receive or pay a series of equal cash pay-

ments for a specific number of periods at equal intervals. An annuity is essentially a

level stream of cash flows for a fixed period of time.

These functions include PMT, IPMT, PPMT, FV, RATE, NPER, and PV.

I can start with an example that you will be familiar with if you read some of the

previous chapters. This example analyzed the payment of a car loan. Consider

the example I used before again: You are purchasing a car for $22,000. You are

required to give $4,000 as a down payment. The annual interest rate is 8.00 percent

and the loan period is three years. Assume end-of-period monthly payments. The

following is the model created for this loan, see Figure 31.1. We named the cells in

column B by the designations in the cells of column A. You will find the example on

the sheet named Car loan in the Chapter 31 workbook.

What is of interest to us in this example is the PMT, the Payment function in cell

B7. Once you understand all the features of this function, you will be able to apply

your know-how to the range of functions previously mentioned. See Figure 31.2 for

the Functions Arguments menu associated with the PMT function.

This function calculates the payment for a loan based on constant payments and a

constant interest rate, as defined within the dialog box. The Excel syntax of the function is:

PMT

ð

rate
,
nper
,
pv
,
fv
,
type

Þ

Where:

rate
is the interest rate per period. In this example, the loan is at an 8 percent

annual interest rate and since the payments are on a monthly basis, the

interest rate per month is 8%/12, or 0.67 percent. You could enter 8%/12,

or Rate/12, 0.67%, or 0.0067, in the field for the
rate
.

nper
is the total number of payment periods in a loan or annuity. In this example,

the loan reimbursement is through monthly payments, therefore your loan has

3*12 or Years*12 periods. You could also enter 36 into the formula for
nper
.

pv
is the present value of the loan. It is also referred to as the principal. You

should always enter it as a negative value in the Excel formula. This amount

in this example is

$18,000.