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.)
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
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.
Search JabSto ::

Custom Search