How to use ISPMT Function in Excel

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:

  1. Rate (required argument) – This is the interest rate on the investment or loan.
  2. Per (required argument) – This is the period for which we want to calculate the interest. It must be an integer between 1 and nper.
  3. Nper (required argument) – The number of periods over which the loan or investment must be paid back.
  4. 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.

ISPMT Function - How to use ISPMT Function in Excel

Syntax:  =ISPMT($B$2/$B$4,E2,$B$3*$B$4,$B$1)

Result:

ISPMT Function in Excel - How to use ISPMT Function in Excel

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.

Add a Comment

Your email address will not be published. Required fields are marked *