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
