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.

Save button

1

•
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

function?

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.

Enter box

PMT function preceded

by negative sign causes

payment to appear as

a positive number

Formula

AutoComplete list

shows alphabetical

list of functions

and names

ScreenTip shows

function and

arguments

Figure 4–15