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

when the security is traded to the buyer.

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: