How to Calculate Term Loan Using IPMT Formula in Excel

Sagar Guide - How to Calculate Term Loan Using IPMT Formula in 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 installment.

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​