PMT Function

​How to Use Home Loan EMI Calculation Using in (PMT Formula) Excel 

EMI is defined by Investopedia as "A fixed payment amount made by a borrower to a lender at a specified date each calendar month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years (Months) , the loan is paid off in full.

PMT is one of the Financial Functions Formula in Excel,  calculates the payment for a loan based on constant payments and a constant interest rate.

PMT FUNCTION FORMULA Syntax is : -

PMT(rate, nper, pv, [fv], [type])

The PMT Function Formula syntax has the following arguments:

• Rate    Mandatory. The interest rate for the loan.
• Nper    Mandatory. The total number of payments for the loan.
• Pv    Mandatory. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
• 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 (zero), that is, the future value of a loan is 0.
• Type    Optional. The number 0 (zero) or 1 and indicates when payments are due.

PMT Function Formula  is used to know the EMI

Here the example for, the amount of the principle paid each period is equal to Rs.5,00,000 divided by 12 = 16666.67

Also notice that the total payment decreases each month as the amount of interest decreases while the principle stays the same.Excel doesn't provide worksheet functions to support term-loan calculations. Therefore, we must use spreadsheet formulas.          
              

Calculating Home Loan Values With one exception, it's quite easy to calculate the values for a Home loan. To illustrate, I'll use the following abbreviations. In parentheses I show the values from the example above.




Loan amount of the : 500000

Interest Rate :  8.35% per Annual

Years : 1

Payments for year : 12




Using these abbreviations, here are the formulas for a Home 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 Screen Shot

​