by The Excel ISPMT function calculates the interest paid during 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.
Syntax:= ISPMT (rate, per, nper, pv)
The ISPMT function syntax has the following arguments:
- Rate (required argument) – This is the interest rate on the investment or loan.
- Per (required argument) – This is the period for which we want to calculate the interest. It must be an integer between 1 and nper.
- Nper (required argument) – The number of periods over which the loan or investment must be paid back.
- Pv (required argument) – This is the present value of the loan/investment. It can be the total value of payments made to date.
Example: Let’s look at some Excel ISPMT function examples and explore how to use the ISPMT function as a worksheet function in Microsoft Excel:
Let’s say you borrowed $10,000,000 from the bank with annual rate of 6.80%, and you make a monthly repayment schedule in which the principle payments are equal. And the repayment will start from January and last for 3 year.
Syntax: =ISPMT($B$2/$B$4,E2,$B$3*$B$4,$B$1)
Result:
In this example, the rate should be converted to monthly rate as 6.80%/12 (B2/B4), the total number of payment periods is 3*12 (B3*B4). The returned interest values are negative, as these represent outgoing payments (for the individual taking out the loan).
Based on the Excel spreadsheet above, the following ISPMT examples would return:
Syntax: =ISPMT($B$2/$B$4,E3,$B$3*$B$4,$B$1)
Result: -55092.59259
Syntax: =ISPMT($B$2/$B$4,E4,$B$3*$B$4,$B$1)
Result: -53518.51852
Syntax: =ISPMT($B$2/$B$4,E5,$B$3*$B$4,$B$1)
Result: -51944.44444
Syntax: =ISPMT($B$2/$B$4,E6,$B$3*$B$4,$B$1)
Result: -50370.37037
Syntax: =ISPMT($B$2/$B$4,E7,$B$3*$B$4,$B$1)
Result: -48796.2963
Syntax: =ISPMT($B$2/$B$4,E8,$B$3*$B$4,$B$1)
Result: -47222.22222
Syntax: =ISPMT($B$2/$B$4,E9,$B$3*$B$4,$B$1)
Result: -45648.14815
Syntax: =ISPMT($B$2/$B$4,E10,$B$3*$B$4,$B$1)
Result: -44074.07407
Syntax: =ISPMT($B$2/$B$4,E11,$B$3*$B$4,$B$1)
Result:-42500
Syntax: =ISPMT($B$2/$B$4,E12,$B$3*$B$4,$B$1)
Result:-40925.92593
Syntax: =ISPMT($B$2/$B$4,E13,$B$3*$B$4,$B$1)
Result: -39351.85185
Note:
- Make sure that you are consistent about the units you use for specifying rate and nper
- If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper.
- If you make annual payments on the same loan, use 12% for rate and 4 for nper.
- For all the arguments −
- Cash you pay out, such as deposits to savings or other withdrawals, is represented by negative numbers.
- Cash you receive, such as dividend checks and other deposits, is represented by positive numbers.
