IPMTfunction 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.
- How to use
- Examples of using
IPMTformula not working?
- Similar formulas to
IPMT formula with the syntax shown below, it has 4 required parameters and 2 optional parameters:
- rate (required):
The interest rate per period of the loan or investment.
- period (required):
The period for which you want to calculate the interest payment.
- number_of_periods (required):
The total number of payment periods for the loan or investment.
- present_value (required):
The present value or principal amount of the loan or investment.
- 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.
- 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.
ExamplesHere are a few example use cases that explain how to use the
IPMTformula 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.
IPMTnot working? Here are some common mistakes people make when using the
IPMTGoogle 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.
The following functions are similar to
IPMT or are often used with it in a formula:
PPMTfunction 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.
PVfunction 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.
FVfunction 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.
You can learn more about the
IPMT Google Sheets function on Google Support.