How to Use the ISPMT Function in Excel

This guide explains how to use ISPMT Function in Excel, including its syntax, required arguments, practical examples, and common errors.

Description: The function calculates the interest of an investment for a specific period.

Syntax: ISPMT (rate, per, nper, pv) .

Inside:

- rate: The interest rate of the investment, which is a required parameter.

- per: The period you want to calculate interest, the value determined from 1 -> nper, is a required parameter.

- nper: Total number of terms in the investment process, which is required.

- pv: Present value of the investment, where the investment is the borrowed value, pv is the amount borrowed to invest, pv is the required parameter.

Attention:

- Between nper and pv, it is necessary to be consistent about units, for example, if nper is calculated by month, then pv is required to be monthly.

- With the payables shown as negative numbers, and amounts such as dividend checks, deposits are represented by positive numbers.

For example:

Calculate the amount of interest payable in the first and second month of an investment knowing the following parameters:

- Calculating interest payable in the first month is:

In the cell to be calculated enter the following formula: = ISPMT (C14 / 12, C15, C16 * 12, C17) .

Because interest is calculated in the 1st month, the interest rate is divided annually c14 / 12 and the total investment period is calculated monthly C16 * 12.

Pressing Enter results (because the interest value should be negative):

So with an investment of 600 million loans at the interest rate of 0.326 the first month to pay more than 16 million interest.

- Calculating interest payable in the 2nd year.

In the cell to calculate enter the formula: = ISPMT (C14,2, C16, C17) .

Pressing Enter results (because the interest value should be negative):

So if in the 2nd year, the investment is not paid, it must pay interest of over 130 million.

The above is a detailed guide on how to use the ISPMT function. Hope to help you in determining the amount of interest to pay from there in the right direction.

Good luck!