IPMT: Defining and 10 Ways of using it

The IPMT is a function in Excel that would return with the payment's interest for the period that has been given for the investment that is dependent on the constant payment, periodic along with a stable interest rate.

Syntax and Arguments

The IPMT has a rate, per, nper, pv (present value), fv (future value), and type, and the formula has these following components: IPMT(rate;per;nper;pv;[fv];[type]).

Rate: This is essential for the function. It is the rate of interest for each period

Per: This is a required part of the function. It is the period for which a user desire to find the interest range, and must be in range 1 to the nper (another part of the function).

Nper: This is required, and described as the quantity of payment for the complete period of annuity.

Pv: This is a required part of the function, and known as the present value, or a lump-sum amount that the future payments would currently worth.

Fv: This is the optional part of the function, and is known as the future value. It is also identified as the cash balance that the user wishes to get after all payments were made. If this is omitted, then the function would assume it to be 0.

Type: This is optional, and can either be 0/1, and act as an indication of when payments are to be due. It could be omitted, but then it would be assumed as 0.

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.

A Simple IPMT Formula

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.

IPMT with 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.

Complete IPMT Formula

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.

IPMT Dealing with a Complicated Client

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.

A Client in Deep Debt

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.

Max and IPMT

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.

IPMT MIN and MAX

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.

IPMT and AVERAGE

Example 9: IF and IPMT

This example make use of acknowledgement of using both IF and IPMT to know the result.

IF and IPMT

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.

Double IPMT Formula