Microsoft Office Tutorials and References
In Depth Information
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
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
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
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:
rate, per, nper, pv, fv, type
FIGURE 32.8 The Principal Function Menu