Microsoft Office Tutorials and References

In Depth Information

The
PMT
function is a bit more complex. It calculates payments due on a loan, assuming a

constant interest rate and constant payments. To perform its calculations, the
PMT
function

requires an interest rate, the number of payments, and the starting balance. The elements

to be entered into the function are called
arguments
and must be entered in a certain order.

That order is written as
PMT(rate, nper, pv, fv, type)
. The following table summarizes the

arguments in the
PMT
function.

Argument

Description

The interest rate, to be divided by 12 for a loan with monthly payments,

by 4 for quarterly payments, and so on

rate

The total number of payments for the loan

nper

The amount loaned (
pv
is short for
present value
, or principal)

pv

The amount to be left over at the end of the payment cycle (usually left

blank, which indicates 0)

fv

0 or 1, indicating whether payments are made at the beginning or at the

end of the month (usually left blank, which indicates 0, or the end of

the month)

type

If Consolidated Messenger wanted to borrow $2,000,000 at a 6 percent interest rate

and pay the loan back over 24 months, you could use the
PMT
function to figure out

the monthly payments. In this case, the function would be written
=PMT(6%/12, 24,

2000000)
, which calculates a monthly payment of $88,641.22.

You can also use the names of any ranges you defined to supply values for a formula. For

example, if the named range NortheastLastDay refers to cells C4:I4, you can calculate the

average of cells C4:I4 with the formula
=AVERAGE(NortheastLastDay)
. With Excel, you

can add functions, named ranges, and table references to your formulas more efficiently

by using the Formula AutoComplete capability. Just as AutoComplete offers to ill in a

cell’s text value when Excel recognizes that the value you’re typing matches a previous

entry, Formula AutoComplete offers to help you ill in a function, named range, or table

reference while you create a formula.