Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Entering conditions for the loan and the monthly payments

Figure 2-25

loan conditions

payment conditions

you will enter a Financial

function here to calculate

the payment amount

Paying off this loan will require 12 payments per year for 30 years, or 360 total pay-

ments. But how much will Amanda have to pay each month? To answer that question, you

can use one of Excel’s Financial functions.

Using the PMT Function

The monthly payment required to pay off a loan can be determined using the
PMT

function
. The syntax of the PMT function is

=PMT(
rate
,
nper
,
pv,
[
fv
=0], [
type
=0])

where
rate
is the interest rate per payment period (determined by dividing the annual inter-

est rate by the number of payment periods in a year),
nper
is the total number of payments,

and
pv
is the present value of the future payments that will be made. In the case of a loan,

the
pv
argument must be entered as a negative number. There are two optional parameters

in this function:
fv
and
type
. The
fv
parameter indicates the future value of the loan and has

a default value of 0. A future value of 0 means that the loan is paid off completely. The
type

parameter specifies whether payments are due at the beginning of the period (
type
=1) or at

the end (
type
=0). The default value of the
type
parameter is 0.

Note that you can also use the PMT function for investments in which a specified

amount of money is saved each month at a specified interest rate. In that case, the value

of the
pv
argument would be positive since it represents an investment (a positive cash

flow) rather than a loan (a negative cash flow).

Because the PMT function, like many Excel functions, has several required arguments,

in addition to some optional arguments, you might not always remember all of the func-

tion’s arguments and the order in which they should be entered. To make your task easier,

you’ll use the Insert Function dialog box to determine the payment amount for the loan

Amanda is considering.