Sunday, August 26, 2018

How to calculate EMI for a loan amount using PMT formula in MS Excel

Watch this youtube video to see screen recording of EMI calculation in MS Excel using PMT formula.

Banks calculate your loan EMIs through two most common methods; Reducing Balance Method and Fixed Balance Method. Reducing balance method is based on time value of money concept. Whereas Fixed balance method is based on simple interest formula.

Reducing balance method calculates interest value at the end of every period (read month). Interest value is calculated on the outstanding amount (principle) at the end of the period. EMI amount is higher than the calculated interest amount. the difference between EMI amount and calculated interest is the principle contribution (pay off) towards your loan.

After this EMI payment against the loan; outstanding balance will be reduced by the principle pay-off above

At the end of next period interest will be calculated on the new outstanding loan balance. Since, the new outstanding balance is a reduced amount, interest amount calculation will be lower. With EMI amount being constant the principle pay off will be higher than previous period's principle pay-off. The decrease in interest amount equals increase in principle pay-off.

Every subsequent period (read month) outstanding loan balance will reduce, resulting decrease in interest amount and increase in principle pay-off amount

Let us take following example to further understand Reducing balance concept:

Home Loan Amount: ₹50,00,000 (₹50 Lacs)
Interest Rate          :      9.00%
Tenure (Years)  :      20

Reducing balance period of this loan is month. Hence, EMI will be paid every month. Annual interest rate of loan is 9%. Hence, per month interest of loan is 0.75%. If this loan began at the start of FY2018-19 (April 2018) the interest calculated on the loan of 50 lac was 37 thousand 500 rupees; as shown in below image.

50 Lac * 0.0075 = 37,500

Suppose the subscriber to this loan decided to pay Rupees 40,000 towards EMI, his contribution towards the principle was Rupees 2,500. Hence outstanding balance after the EMI payment was Rupees 49,97,500.

Next month, his loan book will have calculation as below

As you can see above interest outgo is reduced by 18.75 rupees and principle contribution in loan pay-off is increased by 19 rupees. This happened due to of lower outstanding principle amount in May 2018. This way, every subsequent month outstanding principle of the loan is reduced as seen below

If we drag these calculations; we will come to know when outstanding principle will become negative. As we see below 371st month will be when the subscriber will pay his last EMI of Rs. 40,000 and he will offset this loan in 372nd month by paying off 2513 rupees as outstanding principle and 19 rupees as interest.  Thus, the loan started in April 2018 will be closed down after 31 years in March 2049.

In this example we have a constant interest rate of 9% and steady timely paid EMIof Rs. 40,000.

In above chart amount was considered as the constant based on which tenure was calculated. If we make tenure as the constant; lets say 20 years we will need a method to derive on the amount which will help us to get outstanding amount as 0 after 240th EMI of the loan.

PMT formula in excel can help you to get EMI amount calculated. PMT is short form of payment which helps to know how much be paid towards servicing a loan with constant payment and interest rate. PMT formula needs following values to calculate.

Rate of Interest
Present Value (loan amount)
Future Value (Take 0 to for loan closure)
Type of EMI calculation (0 for payment done at end of month, 1 for begin of month)

Lets take above data as an example to calculate EMI amount.
Rate of Interest as 9% => Monthly Interest will be 0.75%
Number of periods = 240 for a 20 year loan
Present Value (loan amount) = 50 Lacs.
Future Value = 0
Type of EMI calculation (0 for payment done at end of month, 1 for begin of month)

EMI Amount calculated as

Above amount calculated as end of period payment. For begin of period payment the amount will be Rs. 44,651. One can save more every month if they tie up with their lender for a begin of period servicing towards his reducing balance method loan.

With this arrangement, the monthly loan book chart of the subscriber will look as below

and his 240th installment will completely service his loan.

Above description was related to reducing balance method loan. Fixed balance method loan has a different mechanism to calculate EMI. Home loans are calculated using reducing balance method, whereas car loans from private lenders in India are serviced through Fixed Balance Method mechanism. Watch the video in below link to understand the major difference in two mechanisms.