Microsoft Office Tutorials and References

In Depth Information

**The IPMT function**

Suppose you want to take out a 30-year mortgage for $300,000. Assuming an interest rate

of 3.2 percent, what will your monthly payments be? First, divide the 3.2 percent interest

rate by 12 to arrive at a monthly rate (2.66 percent). Next, convert the number of periods

into months by multiplying 30 by 12 (360). You can include these computations as

arguments by using the formula =PMT((3.2%/12), (30*12), 300000) to compute the monthly

mortgage payment, which turns out to be –$1,297.40. (The result is negative because it’s a

cost to you.)

TROUBLESHOOTING

The PMT function produces unrealistic results

Sometimes you might find that the PMT function seems to produce unrealistic

results—such as payments that are excessively large. As you should with all functions

used for calculating investments, make sure you are using the same units for both

the
rate
and
nper
number of periods
) arguments. If, for example, you type 3% for the
(

rate, you must type the
nper
argument in years because 3 percent is an annual rate. If

you type 3% for the rate and 360 as the term, Excel returns the payment required to

amortize a loan at either 3 percent per month for 30 years or 3 percent per year for 360

years! You can resolve your problem by dividing 3 percent by 12 (which is the standard

way of expressing a loan) or typing 30 for
nper
, indicating the term in years. Note,

however, that these two options are not equivalent—they yield very different results

because of the way interest is calculated. You should use the same units your lender

uses, which is probably the annual interest rate divided by 12 and
nper
expressed in

months.

The IPMT function

The IPMT function computes the interest part of an individual payment made to repay an

amount over a specified time period with constant periodic payments and a constant

interest rate. This function takes the arguments
rate
,
period
,
number of periods
,
present value
,

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

Suppose you borrow $200,000 for 30 years at 3 percent interest. The formula

=IPMT((3/12)%, 1, 360, 200000) tells you that the interest component of the payment due

for the first month is an even –$500.00. The formula =IPMT((3/12)%, 360, 360, 200000) tells

you that the interest component of the final payment of the same loan is –$2.10.