ISPMT Function in Google Sheets

Calculate the interest paid during a specific period of an investment using the ISPMT function.

ISPMT Function

The ISPMT function in Google Sheets calculates the interest paid during a specific period of an investment.

Syntax

ISPMT(rate, period, number_of_periods, present_value)
  • rate: The interest rate per period. This is a required parameter.
  • period: The specific period for which you want to calculate the interest. This is a required parameter.
  • number_of_periods: The total number of payment periods. This is a required parameter.
  • present_value: The present value, or the total amount that a series of future payments is worth now. This is a required parameter.

Examples

  1. Interest Calculation for a Specific Period

Calculate the interest for the 2nd period on a $1000 loan with an annual interest rate of 5% over 5 years:

=ISPMT(5%/12, 2, 60, 1000)

This will output approximately -$8.06.

Notes

  • ISPMT is used for calculating the interest part of a payment before the loan amortization is calculated.
  • IPMT: Calculate the interest portion of a payment for a specific period.
  • PMT: Calculate the total payment per period.