Microsoft Office Tutorials and References
In Depth Information
To Enter the PMT Function
The next step is to determine the monthly payment for the loan in cell E4. You can use Excel’s PMT function
to determine the monthly payment. The PMT function has three arguments — rate, payment, and loan amount. Its
general form is:
=PMT(rate, periods, loan amount)
where rate is the interest rate per payment period, periods is the number of payments, and loan amount is the
amount of the loan.
In the worksheet shown in Figure 4–14 on the previous page, Excel displays the annual interest rate in cell E2.
Financial institutions, however, calculate interest on a monthly basis. Therefore, the rate value in the PMT function
is rate / 12 (cell E2 divided by 12), rather than just rate (cell E2). The periods (or number of payments) in the PMT
function is 12 * years (12 times cell E3) because there are 12 months, or 12 payments, per year.
Excel considers the value returned by the PMT function to be a debit and, therefore, returns a negative number
as the monthly payment. To display the monthly payment as a positive number, begin the function with a negative
sign instead of an equal sign. The PMT function for cell E4 is:
–PMT(rate / 12, 12 * years, loan_amount)
monthly interest rate number of payments loan amount
The following steps use the keyboard, rather than Point mode, to enter the PMT function to determine the
monthly payment in cell E4.
• Select cell E4. Type –pmt(rate /
12, 12*years, loan_amount
as the function to display the PMT
function in cell E4 and in the
formula bar (Figure 4–15).
What happens as I enter the
The ScreenTip shows the general
form of the PMT function. The
arguments in brackets in the
ScreenTip are optional and not
required for the computation
described here. The Formula
AutoComplete list shows
functions and cell names that
match the letters that you type
on the keyboard. You can type
the complete cell name, such as
Loan_Amount, or select the cell
name from the list. Excel will add
the closing parenthesis to the
function automatically. Excel also
may scroll the worksheet to the
right in order to accommodate the
display of the ScreenTip.
PMT function preceded
by negative sign causes
payment to appear as
a positive number
list of functions