Buzz
The ISPMT function in Excel is your go-to tool for calculating interest within a specific investment period. To grasp the syntax and usage of the ISPMT function, follow along with the insights shared in this article.
ISPMT Function and Examples
Guide to Using ISPMT Function in Excel - Illustrated Examples
Syntax: ISPMT(rate, per, nper, pv)
Where:
- Rate: Interest rate of the investment, a mandatory parameter
- Per: Period for interest calculation, ranging from 1 to nper, a mandatory parameter
Consider the Example:
Given the spreadsheet below. The input data corresponds to the parameters of the ISPMT function in Excel.
Requirements:
- Calculate the interest paid on the first monthly payment of the loan
- Calculate the interest paid in the first year
- Calculate the interest paid on the first monthly payment. Enter the formula in cell C10 = ISPMT(C6/12,C7,C8*12,C9). The result is shown in the figure below. Where:
+ Annual interest rate / 12 (months) = monthly interest rate
+ Loan term in years * 12 (months) = Number of months for the loan
To calculate interest paid in the first year, enter the formula in cell C11 =ISPMT(C6,1,C8,C9). The result is displayed below.
Above, we've guided you on how to use the ISPMT function in Excel to calculate interest for specific investment or loan terms. You can apply the ISPMT function across Office 2013, Office 2010, Office 2007, and Office 2003 versions.
