I. Intro
The IPMT function calculates the interest paid for a loan with a fixed payment schedule and interest rate.
II. About the function
- Formula: =IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
- Arguments:
- rate (required): The interest rate.
- period (required): The payment period in question. It must be between 1 and number_of_periods.
- number_of_periods (required): The total number of payment periods for this loan.
- present_value (required): The present value, or the sum of the current value of a series of future payments; also known as the principal.
- future_value (optional): The future value, or the cash balance you would like to receive after the last payment. If this argument is omitted, then it's assumed to be 0, meaning the future value of the loan is 0.
- end_or_beginning (optional): The numbers 0 and 1 represent whether payments are due at the beginning or at the end of a period respectively. If omitted, it's assumed to be 0.
- future_value (optional):
- Example: =IPMT(1%,1,360,100000,30000,0)
- Note:
- Ensure that the units for the interest rate and number_of_periods are consistent. For a four-year loan on an interest rate of 12% that is paid monthly, the rate should be 12%/12, and number_of_periods should be 4*12. For the same loan that is paid annually, then the rate should be 12%, and number_of_periods should be 4.
- Cash outflow is represented by negative numbers and cash inflow is represented by positive numbers.
III. Steps
Use the IPMT function
- Select a cell and enter =IPMT.
- Enter the arguments in the cell. Below are formulas for two types of payment: monthly and annually.
- =IPMT(B2/12,B3,B4*12,B5)
- =IPMT(B2,3,B4,B5)
- Press Enter to get the result.
250px|700px|reset
Delete the IPMT function
Select the cell with the IPMT function and press Delete.