Friday, 17 February 2017

IPMT Function Formula in Excel

​How to Calculate Term Loan Formula Using in (IPMT) Excel

 

Term loan is a monetary loan that is repaid in regular payments over a set period of time. Term loans use a different technique. Each period, you pay the amount of interest due plus a fixed amount for principle reduction. As a consequence, your payments decrease over time.

IPMT Function Formula Description is Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.


IPMT FUNCTION FORMULA SYNTAX is : -
IPMT(rate, per, nper, pv, [fv], [type])

The IPMT Function Formula syntax has the following arguments are :

  • Rate    Mandatory. The interest rate per period.
  • Per      Mandatory. The period for which you want to find the interest and must be in the range 1 to nper.
  • Nper   Mandatory. The total number of payment periods in an annuity.
  • Pv       Mandatory. ​The present value, or the lump-sum amount that a series of future payments is worth right now.
  • Fv       Optional.  The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
  • Type    Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

IPMT Function Formula is used to know the interest portion in the EMI of a particular instalment.

Here the example for, the amount of the principle paid each period is equal to Rs.1,00,000 divided by 12 = 8333.33
Also notice that the total payment decreases each every month as the amount of interest decreases while the principle amount stays the same.        
     
Calculating Term Loan Values With one exception, it's quite easy to calculate the values for a Term loan. lam  use the following Following Formulas I show the values from the example above.



Loan amount of the : 100000

Interest Rate :  .5% per Annual

Principle Payment :  amount of the the Month  8333.33 per month).
Total number of loan payment Months : 12


Calculation the number of loan payments that we choose to calculate from the beginning of a loan. In the above example, this number could range from 1 to 12



Using these abbreviations, here are the formulas for a term loan:

  • Principle Payment With Interest Formula : =100000/12+C9
  • Interest Formula : =IPMT(0.5%,A9,12,-E8)
  • Principle Amount Formula : =SUM(B9-C9)
  • Outstanding Amount Formula : =E8-D9

Here the Below Screenshot Image
 

 

5 comments:

  1. Sagar, thanks for the useful guide. I am side by side trying to practice this on my sheet. I never get the formulas correctly placed in the first time.

    ReplyDelete
    Replies
    1. formula clearly mentioned and view the screen shot


      Principle Payment With Interest Formula : =100000/12+C9
      Interest Formula : =IPMT(0.5%,A9,12,-E8)
      Principle Amount Formula : =SUM(B9-C9)
      Outstanding Amount Formula : =E8-D9

      Delete
    2. and Subscribe to my youtube channel : https://goo.gl/WxhAnW

      Delete
  2. MS Excel is not an easy application for a lot of students. This article is very helpful for a lot of struggling students. Thank you for sharing.

    ReplyDelete
    Replies
    1. Thanks for your comment

      Subscribe to my youtube channel : https://goo.gl/WxhAnW

      Delete