Page 336 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 293
Creating Formulas for Financial Applications
TABLE 16.1
Description
Function Argument
The interest rate per period. If the rate is expressed as an annual interest rate, you must divide it
rate
by the number of periods.
nper
The total number of payment periods.
per
A particular period. The period must be less than or equal to nper.
The payment made each period (a constant value that does not change).
pmt
The future value after the last payment is made. If you omit fv, it is assumed to be 0. (The future
fb
value of a loan, for example, is 0.)
type
Indicates when payments are due — either 0 (due at the end of the period) or 1 (due at the
beginning of the period). If you omit type, it is assumed to be 0.
The PMT function Financial Function Arguments 16
The PMT function returns the loan payment (principal plus interest) per period, assuming constant payment
amounts and a fixed interest rate. The syntax for the PMT function is
PMT(rate,nper,pv,fv,type)
The following formula returns the monthly payment amount for a $5,000 loan with a 6 percent annual per-
centage rate. The loan has a term of four years (48 months).
=PMT(.06/12,48,-5000)
This formula returns $117.43, the monthly payment for the loan. Notice that the third argument (pv, for
present value) is negative and represents money owed.
The PPMT function
The PPMT function returns the principal part of a loan payment for a given period, assuming constant pay-
ment amounts and a fixed interest rate. The syntax for the PPMT function is
PPMT(rate,per,nper,pv,fv,type)
The following formula returns the amount paid to principal for the first month of a $5,000 loan with a 6
percent annual percentage rate. The loan has a term of four years (48 months).
=PPMT(.06/12,1,48,-5000)
The formula returns $92.43 for the principal, which is about 78.7 percent of the total loan payment. If I
change the second argument to 48 (to calculate the principal amount for the last payment), the formula
returns $116.84, or about 99.5 percent of the total loan payment.
NOTE To calculate the cumulative principal paid between any two payment periods, use the
NOTE
CUMPRINC function. This function uses two additional arguments: start_period and
end_period. In Excel versions prior to Excel 2007, the CUMPRINC is available only when you install the
Analysis ToolPak add-in.
293