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

Sagar Guide - ​How to Use Home Loan EMI Calculation Using PMT Formula in 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.2,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 : 200000

Interest Rate :  10% per Annual

Principle Payment :  amount of the the Month  16666.67 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 Home Loan:

Principle Payment With Interest Formula : =200000/12+C9
Interest Formula : =PMT(0.1%,10*12,-E8)
Principle Amount Formula : =SUM(B9-C9)
Outstanding Amount Formula : =E8-D9.