Friday, 17 February 2017

PMT Function Formula in Excel

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

Here the Below Screen Shot

2 comments:

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

  2. Thank you for sharing such great information.
    It is informative, can you help me in finding out more detail on
    home loan interest calculation.

    ReplyDelete