How to use ISPMT Function (WS)

Description

The Microsoft Excel ISPMT function returns the interest payment for an investment based on an interest rate and a constant payment schedule. This function was created for backwards compatibility with Lotus 1-2-3.

Syntax

The syntax for the ISPMT function in Microsoft Excel is:

ISPMT( interest_rate, period, number_payments, PV )

Parameters or Arguments

interest_rate

The interest rate for the investment.

period

The period to calculate the interest rate. It must be a value between 1 andnumber_payments.

number_payments

The number of payments for the annuity.

PV

The loan amount or present value of the payments.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some ISPMT function examples and explore how to use the ISPMT function as a worksheet function in Microsoft Excel:

This first example returns the interest payment for a $5,000 investment that earns 7.5% annually for 2 years. The interest payment is calculated for the 8th month.

=ISPMT(7.5%/12, 8, 2*12, 5000)
Result: -20.83333333

This next example returns the interest payment for a $8,000 investment that earns 6% annually for 4 years. The interest payment is calculated for the 30th week.

=ISPMT(6%/52, 30, 4*52, 8000)
Result: -7.899408284

This next example returns the interest payment for a $6,500 investment that earns 5.25% annually for 10 years. The interest payment is calculated for the 4th year.

=ISPMT(5.25%/1, 4, 10*1, 6500)
Result: -204.75