Microsoft Office Tutorials and References
In Depth Information
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.
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.
The period in which you want to find the interest. This must be between
1 and Nper .
The total number of payments in the period of an investment.
The present value of the investment.
MDURATION returns the modified duration of a security with a par value assumed of $100.
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
when the security is traded to the buyer.
The security’s maturity date—when the security expires.
The security’s annual coupon rate.
The security’s annual yield.
The number of payments per year—Annual = 1; Semiannual = 2;
Quarterly = 4.
The day count basis to use.
The modified duration is calculated as follows: