Ispmt Function

Purpose

Return value

Syntax

=ISPMT(rate,per,nper,pv)

• rate - Interest rate.
• per - Period (starts with zero, not 1).
• nper - Number of periods.
• pv - Present value.

Using the ISPMT function

The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use:

=ISPMT(10%,0,5,-10000) // interest in period 1
=ISPMT(10%,1,5,-10000) // interest in period 2
=ISPMT(10%,2,5,-10000) // interest in period 3
=ISPMT(10%,3,5,-10000) // interest in period 4
=ISPMT(10%,4,5,-10000) // interest in period 5

In the example shown, the formula in H11, copied down, is:

=ISPMT($C$6,B11-1,$C$7,-$C$5)

Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IPMT function .

Notes

1. Be consistent with the units. For a 3 year loan with monthly payments and an annual interest rate of 10%, enter rate as 10%/12. Enter nper as 3*12.
2. ISPMT uses a zero-based index for period ( per ). Use 0 for period 1, 1 for period 2, etc.
3. The PPMT function is for loans with even principal payments. For a loan with even periodic payments, use the IPMT function.

Purpose

Return value

Syntax

=MDURATION(settlement,maturity,coupon,yld,freq,[basis])

• settlement - Settlement date of the security.
• maturity - Maturity date of the security.
• coupon - The security’s annual coupon rate.
• yld - The security’s annual yield.
• freq - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4).
• basis - [optional] Day count basis (see below, default =0).

Using the MDURATION function

In finance, duration is a measure of the price sensitivity to changes in interest rates for an asset that pays interest on a periodic basis, like a bond. Duration can be used by financial managers as part of a strategy to minimize the impact of interest rates changes on net worth. Modified duration is a measure of the expected change in a bond’s price to a 1% change in interest rates.

Excel’s MDURATION function returns the modified Macauley duration for an assumed par value of $100. The Macaulay duration is the weighted average term to maturity of the cash flows from a security, which can be calculated with Excel’s DURATION function .

Example

In the example shown, we want to calculate the modified duration of a bond with an annual coupon rate of 5% and semi-annual payments. The settlement date is 15-Dec-2017, the maturity date is 15-Sep-2027, and the day count basis is US (NASD) 30/360. The formula in F5 is:

=DURATION(C7,C8,C5,C6,C9,C10)

and returns 7.55 years.

Entering dates

In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, you can use the DATE function . To illustrate, the formula below has all values hardcoded, and the DATE function is used to supply each of the two required dates:

=MDURATION(DATE(2017,12,15),DATE(2027,9,15),0.05,0.05,2,0)

Basis

The basis argument controls how days are counted. The DISC function allows 5 options (0-4) and defaults to zero, which specifies US 30/360 basis . This article on Wikipedia provides a detailed explanation of available conventions.

Notes

• In Excel, dates are serial numbers .
• All dates, frequency, and basis are truncated to integers.
• If dates are invalid (i.e. not actually dates) MDURATION returns #VALUE!
• MDURATION returns #NUM when: settlement >= maturity coupon < 0 or yield < 0 Basis is out-of-range