Microsoft Office Tutorials and References

In Depth Information

**The PPMT function**

The PPMT function

The PPMT function is similar to the IPMT function, except it computes the principal

component of an individual payment when a loan is repaid over a specified time with constant

periodic payments and a constant interest rate. If you compute both IPMT and PPMT for

the same period, you can add the results to obtain the total payment. The PPMT function

takes the arguments
rate
,
period
,
number of periods
,
present value
,
future value
, and
type
;

for definitions of these arguments, see Table 16-1.

If you borrow $200,000 for 30 years at 3 percent interest, the formula =PPMT((3/12)%, 1,

360, 200000) tells you that the principal component of the payment for the first month of

the loan is –$343.21. The formula =PPMT((3/12)%, 360, 360, 200000) tells you that the

principal component of the final payment of the same loan is –$841.11.

The NPER function

The NPER function computes the number of periods required to amortize a loan, given a

specified periodic payment. This function takes the arguments
rate
,
payment
,
present value
,

future value
, and
type
; for definitions of these arguments, see Table 16-1.

Suppose you can afford mortgage payments of $2,000 per month and you want to

know how long it will take to pay off a $300,000 loan at 3 percent interest. The formula

=NPER((3/12)%, –2000, 300000) tells you that your mortgage payments will extend over

188 months.

If the payment is too small to amortize the loan at the indicated rate of interest, the

function returns an error value. The monthly payment must be at least equal to the period

interest rate times the principal amount; otherwise, the loan will never be amortized. For

example, the formula =NPER((3/12)%, –500, 300000) returns the #NUM! error value. In this

case, the monthly payment must be at least $751 to amortize the loan (although it would

take more than 220 years worth of payments at that amount).

The RATE function

The RATE function determines the rate of return of an investment that generates a series of

equal periodic payments or a single lump-sum payment. This function takes the arguments

number of periods
,
payment
,
present value
,
future value
,
type
, and
guess
; for definitions of

these arguments, see Table 16-1. You use either the
payment
argument to compute the rate

for a series of equal periodic payments or the
future value
argument to compute the rate of

a lump-sum payment.

Suppose you’re considering an investment that will pay you four annual $1,000

payments. The investment costs $3,000. To determine the actual annual rate of return on your