Friday, April 2, 2010

Excel Formula : PMT Function

In my previous post I shared EMI Calculator with you. In this post I am sharing how to make EMI Calculator. This has been done using PMT Function in excel to calculate EMI amount and Spin Buttons ActiveX Controls to fine tune the amounts.

EMI Calculator



Using PMT Function in Excel


PMT Function Calculates the payment for a loan based on constant payments and a constant interest rate. For calculating EMI you need 3 inputs.

Loan Amount , Annual Interest Rate, Loan Period in Years. Using these figures EMI can be calculated with PMT Function in Excel

EMI Calculator


Syntax : PMT(RATE,NPER,PV,FV,TYPE)

RATE : is the interest rate for the loan. In case you are making monthly payments devide the annual rate by 12 to get monthly rate.

NPER : is the total number of payments for the loan. When payments are made on monthly basis you need to multiply number of years with 12 to arrive at total number of payments.

PV : is the present value, or the total amount that a series of future payments is worth now; also known as the principal. We take this figure as (-) Negative in our formula since it is receipt of funds.

FV : is 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. In our formula we omit this figure as we need to reach zero balance at the end of term.

Type : is the number 0 (zero) or 1 and indicates when payments are due. If payments are due at the end of the period use 0 or omit this figure. use 1 if payments are made at the beginning of the period. In our formula we have omitted this.

We have calculated our figures with following formulas

EMI : =PMT(Interst/12,Years*12,-LoanAmt)

Total Amount with Interest - This is total outgo during the loan term. This can be calculated using : = EMI*Years*12

Flat Interest Rate : = (Total Amount with Interest - LoanAmt)/LoanAmt/Years : Many financiers talk about Flat interest rate while landing money. They calculate simple interest on entire loan amount for entire period of the loan. Many people go for the loan without understanding the actual interest rate. Flat interest rate looks very attractive as it is much lower than the actual interest rate which is calculated on reducing balance. You can see the difference in the figures above. So be careful while borrowing on flat interest rate.

In next post I will cover Adding Spin Buttons ActiveX Controls to Worksheet.

10 comments:

  1. How to create Home loan EMI calculator with features like staggered disbursement, variable interst rates amd moratorium period

    ReplyDelete
  2. I am a banker. We charge interest rate on the outstanding balance every month end. This is known as interest with monthly rests. Could you calculate and send me a blank table wherein if I put the details, the program should calculate emi with monthly rests (ie interest will be charged on outstanding every month) what will be the emi? Could you send the same to me on my email id vinodhemdev@gmail.com This will be very helpful for our customers who wish to know how much emi will be there per lac on monthly rest basis

    ReplyDelete
  3. use pmt formula
    =pmt(5%/12,60,-10000)

    ReplyDelete
  4. Dear Yogesh, Thank you for explaining in very simple way the usage. It is indeed very helpful to all.
    Paresh

    ReplyDelete
  5. Hi I used the same figure at same cells with 14 % , instead of 14.25 as decimals were creating problem and exact same formula but i am getting value 233333.34 . WHy ? do i need to enable something in excel . i checked again the formula and values but i dont get EMI Value that you have got in the example . where i am making mistake ?

    ReplyDelete
  6. I am looking for a formula to calculate the PPF. Each month separately and whole year accumulated interest.
    Thanks

    ReplyDelete
  7. What is formula for calculating EMI of a sim0le interest loan.

    ReplyDelete
  8. What is formula for calculating EMI of a sim0le interest loan.

    ReplyDelete
  9. why do you have taken principal as negative(-) .

    ReplyDelete