How to Use ISPMT Function in Excel

This guide will explain how to use the ISPMT function in Excel.

If you’re keeping track of a loan, you may need to compute the interest paid during a specific period. If your principal payments are always the same amount, we can calculate the interest paid using the ISPMT function in Excel.

The ISPMT function is flexible enough to work with monthly, quarterly, and even weekly payments and can work for loans and investments.

In this guide, we will provide a step-by-step tutorial on how to use the ISPMT function in Microsoft Excel.

The Anatomy of the ISPMT Function

The syntax of the ISPMT function is as follows:

=ISPMT(rate, per, nper, pv)

Let’s look at each argument to understand how to use the ISPMT function.

• ISPMT() refers to our ISPMT function. This function calculates the interest paid or received for a specified period of a loan that follows an even principal payment plan.
• The rate parameter refers to the actual interest rate for the investment
• The per  parameter refers to the period for which you want to find the interest. The given period is specified as a zero-based number. This means that a value of 0 indicates the first period.
• The nper argument refers to the total number of payment periods for the investment.
• The pv argument is the present value of the given investment. When using ISPMT with loans, the pv argument refers to the loan amount.

A Real Example of Using the ISPMT Function in Excel.

Let’s explore a simple example where we can use the ISPMT function in an Excel spreadsheet.

Suppose we have a loan with the following details:

• A loan amount of $5,000
• A total of 4 payment periods
• 6% interest gained in each period

Given these details, we want to find the amount of interest paid for each quarter.

In the table above, we have a table holding details of our loan and another table where we’ll return the interest paid for each period. We can use the following formula to find the interest of the first period:

=ISPMT($B$3,A6,$B$2,$B$1)

We’ll set all arguments other than A6 to an absolute reference ($) to make it easier to copy the formula down the rest of the column. We can set a cell reference to an absolute reference by adding a dollar sign before both the column letter and row number.

After using the formula, we can see that we pay $300 in interest for the first period (period 0) and only $75 in the final period (period 3).

The ISPMT function can also calculate interest paid for loans that are paid monthly rather than quarterly.

In the example above, we’ve used the ISPMT function to calculate the interest paid each period for a loan paid monthly.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the ISPMT function in Excel.

How to Use the ISPMT Function in Excel

1. Identify the details of your loan or investment required to calculate the interest for a given period. We’ll need the principal value (PV), the total number of periods (nper), the rate per period (rate), and the desired period to calculate for (per).
   In our example, we want to find the interest to pay for each period in a loan paid quarterly. To specify the first period, we’ll use a value of 0 for our per argument.
   
2. Type the ISPMT function and specify the interest rate for the period.
   
3. Next, provide an integer to choose what period to calculate interest for.
   
4. Select the cell that indicates the total number of periods.
   
5. Next, provide the principal value of the loan or investment. Hit the Enter key to evaluate the function.
   
6. We can use the AutoFill feature to copy the formula down to find the interest paid for the succeeding periods. Ensure the required arguments are set as absolute references to prevent these values from changing when copying the formula. For example, to set cell B3 as an absolute reference, we can add a dollar sign ($) before the column letter and row number to form $B$3.
   

These are all the steps you need to know to start using the ISPMT function in Excel.

FAQs

1. What is the difference between ISPMT and IPMT in Excel?
   You may be wondering which of these functions you should use since they both calculate interest payments. The IPMT takes into consideration the remaining balance or future payments when calculating the interest, while the ISPMT only calculates the interest of a single period without considering other factors.
   Loans that require even principal payments are best calculated with the ISPMT function. On the other hand, loans with even periodic payments require the use of the IPMT function instead.

To learn more about using Excel to calculate payments for a loan, you can check our guide on how to use the PMT function in Excel. You may also want to read our guide on how to use the NPER function if you need to find the number of periods for an investment or loan.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!