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
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