Page 97 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 83

CREATING FORMULAS 4

The PMT, IPMT, or PPMT functions all have the
same arguments, with the exception of the PMT
function which does not use the per argument.
The following table provides a description of
each argument used by these functions.
ARGUMENT DESCRIPTION

rate The rate charged for each period or payment. If payments are monthly, you divide the
annual interest rate by 12.
per The period, or payment month, for which you want to calculate the interest amount.
nper The total number of periods or payments on the loan. For example, a 5-year loan with
monthly payments has 60 periods.
pv The total amount of the loan.
fv The remaining balance on the loan when the last payment is complete. If this is 0, you can
omit this argument.
type An optional argument to define if payments are made at the beginning or end of each period.
If omitted or 0, Excel calculates interest and payments based on a payment at the end of the
period. If the value of the argument is 1, Excel calculates interest based on payments at the
beginning of each period. In a table, you must use the same type value for all formulas.

‡ In the Principal cell, type ° In the Remaining cell, ‚ Select the formula cells in Note: See Chapter 1 for more on
=-PPMT(rate, per, nper, pv), type a formula that subtracts the first row and copy them copying and pasting cells.
replacing the arguments the Principal cell from the to the second row.
with values. Balance cell, in this example, ■ Excel duplicates the
=B8-D8. — Select the Balance cell formulas in the remaining
■ Add the minus sign to the and the formula cells in the cells, and, if calculated
formulas to display a positive · Set the next Balance cell second row and copy them correctly, the remaining
amount. equal to the result of step 8, down to the end of the table. balance for the final period
here =E8. displays as zero.
83