Microsoft Office Tutorials and References
In Depth Information
ISPMT
ISPMT
ISPMT calculates the interest paid during a defined period of an investment. This function was
provided originally for compatibility with the Lotus 1-2-3 spreadsheet program.
=ISPMT(rate,per,nper,pv)
The units used must be consistent with Rate and Nper . Let’s say, for example, you make monthly
payments on a five-year loan at an annual interest rate of 12%, use 12%/12 for Rate and 5*12 for
Nper . If you make annual payments on the same loan, use 12% for Rate and 5 for Nper .
The interest rate for the investment.
RATE
The period in which you want to find the interest. This must be between
1 and Nper .
PER
The total number of payments in the period of an investment.
NPER
The present value of the investment.
PV
MDURATION
MDURATION returns the modified duration of a security with a par value assumed of \$100.
=MDURATION(settlement,maturity,coupon,yield,frequency,basis)
The MDURATION function is found only if the Analysis Toolpak is installed. It must be turned
on using the Add-Ins command from the Tools menu. Figure 6.22 shows that the settlement
date is 5/15/1999 and the maturity date is 9/15/2004. The coupon’s rate is 7.8% and the yield
of the coupon is 9.2%. You’ll also see that the frequency is semiannual and the basis is
Actual/actual. This means that the modified duration equals 4.18.
The security’s settlement date. This is the date after the issue date
SETTLEMENT
The security’s maturity date—when the security expires.
MATURITY
The security’s annual coupon rate.
COUPON
The security’s annual yield.
YIELD
The number of payments per year—Annual = 1; Semiannual = 2;
Quarterly = 4.
FREQUENCY
The day count basis to use.
BASIS
The modified duration is calculated as follows:
Search JabSto ::

Custom Search