Most consumer loans these days are EMI (equated monthly instalment) based. This means that the principal repayments + interest repayments are adjusted in such a manner that the total repayment every month is the same.
An excel formula can help you quickly estimate the EMI of a loan.
Lets say
- Principal = 500,000
- Interest rate = 12% annual ~ 12%/12=1% monthly
- Repayment term = 1 year = 12*1=12 months
EMI can be calculated by = PMT(rate,nper,pv) = PMT(1%,12,500000)
This gives the EMI to be equal to 44,424.39
Here is an interesting insight into how EMI works
In cell B2 =PMT(1%,12,500000)
In cell C2 =PPMT(1%,A2,12,500000)
In cell D2 =IPMT(1%,A2,12,500000)
The above two formulas calculate the principal and interest component in the EMI respectively.
It is interesting to note that in the earlier EMIs, interest component is high which gradually gets reduced as the principal run-down takes place.
Useful Excel Tips 