IPMT Function

​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.

   
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 : 500000

Interest Rate :  .8.35% per Annual

Years : 1

Payment for year : 12





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

• Payment With Interest Formula : =PMT(0.0835/12,1*12,-500000)
• Interest Formula : =IPMT(0.0835/12,A8,1*12,-500000)
• Principle Amount Formula : =PPMT(0.0835/12,A8,1*12,-500000)
• Outstanding Amount Formula : =E7-D8

Here the Below Screenshot Image