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