Microsoft Office Tutorials and References
In Depth Information
Chapter 31: Frequently Used Financial Functions
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:
rate , nper , pv , fv , type
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