Excel 2010 Bible

Part II: Working with Formulas and Functions

332

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(6%/12,1,48,-5000)

The formula returns $92.43 for the principal, which is about 78.7 percent of the total loan pay-

ment. If I change the second argument to 48 (to calculate the principal amount for the last pay-

ment), 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 CUMPRINC function.
This function uses two additional arguments: start_period and end_period. In Excel versions prior to
Excel 2007, CUMPRINC is available only when you install the Analysis ToolPak add-in. n

IPMT .............................................................................................................

The IPMT function returns the interest part of a loan payment for a given period, assuming con-

stant payment amounts and a fixed interest rate. The syntax for the IPMT function is

IPMT(rate,per,nper,pv,fv,type)

The following formula returns the amount paid to interest 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).

=IPMT(6%/12,1,48,-5000)

This formula returns an interest amount of $25.00. By the last payment period for the loan, the

interest payment is only $0.58.

Note
To calculate the cumulative interest paid between any two payment periods, use the CUMIPMT function. This
function uses two additional arguments: start_period and end_period. In Excel versions prior to Excel
2007, CUMIPMT is available only when you install the Analysis ToolPak add-in. n

RATE .............................................................................................................

The RATE function returns the periodic interest rate of a loan, given the number of payment peri-

ods, the periodic payment amount, and the loan amount. The syntax for the RATE function is

RATE(nper,pmt,pv,fv,type,guess)

The following formula calculates the annual interest rate for a 48-month loan for $5,000 that has a

monthly payment amount of $117.43.

=RATE(48,117.43,-5000)*12

This formula returns 6.00 percent. Notice that the result of the function is multiplied by 12 to get

the annual percentage rate.