September 17, 2021

# 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. 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. 