IPMT Function
The IPMT function in Google Sheets calculates the interest portion of a payment for a specific period of an investment based on constant periodic payments and a constant interest rate.
Syntax
IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
rate: The interest rate per period. This is a required parameter.period: The specific period for which you want to find the interest. Must be between 1 and number_of_periods. 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.future_value: (Optional) The cash balance you want to attain after the last payment is made. Default is 0.end_or_beginning: (Optional) When payments are due: 0 for end of period (default), 1 for beginning of period.
Examples
- Interest Payment Calculation
Calculate the interest payment on a $1000 loan with an annual interest rate of 5% over 5 years for the 2nd period:
=IPMT(5%/12, 2, 60, 1000)
This will output approximately -$4.93.
Notes
- The rate must be adjusted to the period. For example, if the annual interest rate is 5% and payments are monthly, use 5%/12.