How to Use Ipmt Function in Excel

In this lesson, you can learn how to use IPMT function in Excel.

This function calculates the amount of interest you have to pay in a given period, assuming that the interest rate and monthly payment in subsequent periods are fixed. Interest with the amount repayable installment loans are periodically calculated using the PMT function.

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.

See also  How to vlookup on a vlookup in Excel?

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.

See also  How to Use NPER Function in Excel

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.

See also  How to use Sumproduct Function in Excel

Double IPMT Formula