Check Sheet Logo
Check Sheet

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.

Usage

Use the IPMT formula with the syntax shown below, it has 4 required parameters and 2 optional parameters:

=IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
Parameters:
  1. rate (required):
    The interest rate per period of the loan or investment.
  2. period (required):
    The period for which you want to calculate the interest payment.
  3. number_of_periods (required):
    The total number of payment periods for the loan or investment.
  4. present_value (required):
    The present value or principal amount of the loan or investment.
  5. future_value (optional):
    The future value or cash balance you want to attain after the final payment, if omitted this will be assumed to be 0.
  6. end_or_beginning (optional):
    Optional. Indicates whether payments are due at the end of the period or at the beginning of the period. This parameter is set to 0 or omitted for payments due at the end of the period, and 1 for payments due at the beginning of the period.

Examples

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

Calculating the interest portion of a loan payment

IPMT can be used to calculate the interest portion of a loan payment. By using the function in combination with the PPMT function, you can calculate both the principal and interest portions of each loan payment and create a loan amortization schedule.

Determining the total interest paid on a loan

By using the IPMT function to calculate the interest portion of each loan payment, you can determine the total interest paid on a loan over its lifetime. This can be helpful when comparing different loan options or when analyzing the true cost of borrowing.

Calculating the interest earned on an investment

IPMT can also be used to calculate the interest earned on an investment. By using a negative interest rate, you can calculate the interest earned on an investment rather than the interest paid on a loan.

Common Mistakes

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

Missing or incorrect input

One or more of the required input values is missing or contains incorrect data. Double-check the input values and ensure they are in the correct format.

Incorrect sign for input values

Ensure that the input values have the correct sign. For example, if the present value is a negative number, make sure to include the negative sign in the input.

Incorrect order of input values

Make sure to input the values in the correct order as specified in the syntax. For example, ensure that the rate input comes before the period input.

Incorrect use of brackets

If including the future value or end_or_beginning inputs, ensure that they are correctly enclosed in square brackets as indicated in the syntax.

Incorrect use of period input

The period input should be a whole number that is within the range of the number_of_periods input. Double-check that the period value is correct.

Related Formulas

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

  • PPMT

    The PPMT function calculates the payment on the principal of an investment or loan given the interest rate, number of periods, and present value. It is commonly used in financial analysis to calculate the principal portion of a loan or investment payment. The function returns a negative number because it represents a payment toward the principal balance, which reduces the overall balance.

  • 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 IPMT Google Sheets function on Google Support.