Microsoft Office Tutorials and References
In Depth Information
Entering conditions for the loan and the monthly payments
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.