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.
The interest rate, to be divided by 12 for a loan with monthly payments,
by 4 for quarterly payments, and so on
The total number of payments for the loan
The amount loaned ( pv is short for present value , or principal)
The amount to be left over at the end of the payment cycle (usually left
blank, which indicates 0)
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
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.