Microsoft Office Tutorials and References

In Depth Information

**Interest—IPMT Function**

nper
is the total number of payment periods in an annuity.

pv
is the present value

—

the total amount that a series of future payments is

worth now.

fv
is the future value, or the cash balance you want to attain after the last

payment is made. If
fv
is omitted, it is assumed to be 0 (zero), that is, the

future value of the loan after the last payment would be 0.

type
is the number 0 or 1 and indicates whether payments are due at the start (1)

or at the end (0) of each period.

In Figure 32.8 you can see the Function Arguments menu for PPMT. Note that

the
type
field is not visible on the menu. Use the scroll bar on the right side of the

menu to make it visible.

The monthly Principal part in this example for the first period

month 1 is only

$865.81 of the $3,865.81 monthly payment. As was just pointed out, and is also a

well-known fact, the principal repayment portion is very low at the beginning of the

loan and the interest portion

—

is high.

Double-click on the drag handle of the just calculated PPMT result in cell G2 and

Excel will AutoFill the series until the end of the defined periods. Note again the big

differences in the principal amounts from the first to the last months

—

as you will calculate in the next step

—

’

amounts. See

Figure 32.9.

Interest

IPMT Function

The IPMT function will calculate the monthly interest for each of the 300 months.

Interest amounts vary monthly as they are calculated on the remaining balance of the

principal portion. The interest portion is very high at the beginning of the loan and

gradually decreases, getting low toward the end. The IPMT has all the components

of the PMT, the payments function, with an additional input, the period numbers.

It is the similar to the PPMT:

—

IPMT

ð

rate, per, nper, pv, fv, type

Þ

FIGURE 32.8
The Principal Function Menu