In this lesson, you can learn how to use IPMT function in Excel.
The IPMT function in Excel calculates the interest payment for a given period of an investment or loan, assuming a fixed interest rate and constant periodic payments. This is useful for understanding how much of each payment is going towards interest over the life of the loan.
IPMT function syntax
The syntax of this function, the formula is as follows:
=IPMT(rate,per,nper,pv,[fv],[type])
- rate – the interest rate period – the period for which interest is calculated
- per – the specific payment period for which you want to calculate the interest (the range of values is from 1 to nper)
- nper – Total number of payments during the loan repayment or saving
- pv – the current value of the loan taken
- fv – the amount you intend to accumulate in your account after the last payment
- type – 0 is a payment at the beginning of the period and 1 is a payment at the end of the period
Examples of Ipmt function
Example 1: A Simple IPMT Formula
We are considering lending money to a client. The problem is, we do not know how much the investment would worth in the end. We finds that the IPMT function is the solid tool for finding answers for acknowledging the calculation.
Example 2: IPMT with More Details
In our investment bank company, we have a small problem, and we do not know how to best acknowledge it. We are trying to find the comprehensive and more tangible value for IPMT. This allows the possibilities of using the IPMT with a more details.
Example 3: Complete IPMT Formula
We have a client with this urge to be more difficult than other clients that we are used to, and would desire putting down some money, while desiring that the value should not worth more than 200,000 in the future.
We find this to be extremely difficult, but with the help of IPMT, we can find out how it would work out.
Example 4: IPMT Dealing with a Complicated Client
Our client is being quite complicated, with a rare desire. The whole calculation was already set, but the client has come and make a strange demand, which is his ability to add certain amount of money to the down payment.
This is why we were going to use the IPMT, and add the amount of money that the client wishes to add to the money. We are using the previous data for finding out, if it would be valuable.
Example 5: A Client in Deep Debt
We don’t know if the IPMT would solve the problem, but we belief that it worth a try. The client has multiple debts that he cannot relief. But, he is now considering a new loan that would be combined into the previous loan. He is taking advantage of our new decreased interest rate.
For the client, we’d know we need to describe all the debts in the same Excel document for an overview.
Example 6: Max and IPMT
The company is considering helping the client. However, we also need to consider or policy that clearly state that the only debt we would accommodate into the new loan policy that the client would be given.
The policy states that, we could only accommodate the max of all the respective debts, and the client would have to leave out the remaining debts for something else.
Example 7: IPMT, MIN and MAX
We’d have complicated policy that enhance the investment value. This is the perfect place, where we would like to rather operate in a more complicated manner.
The business is doing great, and we would know how many stocks their debts would cost us. This complication now lead to us acknowledging the full evaluation of the value. This is why we are now multiplying the max with the min on the IPMT formula.
Example 8: IPMT and AVERAGE
The whole business is going quite well. But, this time we’d need to multiply the value of IPMT with the average. This is why we use the IPMT in combination to the interest payment of it, and then multiply it with the average.
Example 9: IF and IPMT
This example make use of acknowledgement of using both IF and IPMT to know the result.
Example 10: Double IPMT Formula
We need the acknowledgment of interest payment of a client who has two different loan with the company. This is why we are using the IPMT formula, and add them together to know the interest of both, so we would acknowledge the amount of money we would make from both payment interest.
