Check Sheet Logo
Check Sheet

ISPMT

The ISPMT function calculates the interest paid during a specific period of a loan or investment, based on constant payments and a constant interest rate. This function is commonly used in financial analysis and forecasting.

Usage

Use the ISPMT formula with the syntax shown below, it has 4 required parameters:

=ISPMT(rate, period, number_of_periods, present_value)
Parameters:
  1. rate (required):
    The interest rate per period of the loan or investment.
  2. period (required):
    The period for which to calculate the interest paid.
  3. number_of_periods (required):
    The total number of periods for the loan or investment.
  4. present_value (required):
    The present value, or principal amount, of the loan or investment.

Examples

Here are a few example use cases that explain how to use the ISPMT formula in Google Sheets.

Calculating interest paid on a loan

Use ISPMT to calculate the interest paid during a specific period of a loan with constant payments and interest rate.

Forecasting returns on an investment

Use ISPMT to forecast the interest earned during a specific period of an investment with constant payments and interest rate.

Common Mistakes

ISPMT not working? Here are some common mistakes people make when using the ISPMT Google Sheets Formula:

Incorrect interest rate

One common mistake is to use an annual interest rate instead of the rate per period, which can result in incorrect interest payments.

Incorrect period number

Another common mistake is to use an incorrect period number, such as using a 1-based index instead of a 0-based index, which can result in an incorrect interest payment.

Related Formulas

The following functions are similar to ISPMT or are often used with it in a formula:

  • IPMT

    The IPMT function calculates the interest payment for a given period of an investment or loan. It is commonly used to determine the interest portion of a loan payment. This function can be helpful when creating loan amortization schedules or when analyzing the cost of borrowing money.

  • PV

    The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.

  • FV

    The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

Learn More

You can learn more about the ISPMT Google Sheets function on Google Support.