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

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

**Here the Below Screen Shot**

Wow, great guide thanks, man. I didn't even know that this much we can do on excel. I just used excel for making simple sheets. But you provided a great help. thanks

ReplyDelete

ReplyDeleteThank you for sharing such great information.

It is informative, can you help me in finding out more detail on

home loan interest calculation.