Being associated with bank for short-term, I often get this question from friends and relatives. How to calculate EMI? When you apply for loan, they will just tell you the EMI, but does not tell you how they are calculated unless you ask them specifically to show how they calculated it. EMI table is also called as amortization table, which shows the balance at each installment.
EMI or equated monthly installments is the most popular form of loan payment. It is a fixed amount of repayment made every month towards the loan, which includes payment towards both principal and interest.
It is easy to prepare an EMI table in Excel sheet. There is a financial function called PMT to calculate the EMI.
PMT(rate,nper,pv)
rate – Interest rate for the loan.
nper – Total number of payments for the loan.
pv – Present value/principal.
Prepare excel sheet with the following data and enter the PMT function in the cell “C7.”
- Rate = C6/C5, which divides rate of interest by number of months thus giving rate of interest per month.
- Nper = C4*C5, which gives total number of months we need to pay equated installment.
- Do not forget to take the loan amount in minus value, so that our EMI table shows positive values.
Now prepare table with Serial Number of Payment, Monthly Installment, Interest Amount, Principal Amount, and Balance. Take the first row with serial number “0” and in the balance amount point the cell to “Loan Amount” – type “=C3” Next Enter the serial number as 1. Monthly installments will remain same so take absolute cell reference (by adding $) which will not change when you drag the cells to auto increment. In “Monthly Installment” column, enter “=$C$7“. In the “Interest Amount” column, enter “=F10*$C$6/$C$5” which means we are dividing the balance amount at the end of the month by monthly interest rate (rate of interest/payments per year). As the rate of interest and yearly payments are not going to change, we used absolute reference using $ sign. In “Principal Amount” column type “=C11-D11” which deducts the monthly interest from monthly EMI and whatever the balance available shows as payment towards the principal amount. In “Balance” column enter,” =F10-E11“, which means we are deducting the principal amount in the EMI to ascertain the original balance at the end of the month upon which we are going to calculate interest for the next month. Now select the row and drag it until balance becomes “0”.
That’s all! We made our own EMI calculator in Excel. This Excel EMI calculator is created for a tenure of 20 years. If you are just calculating for 5 years, i.e., 60 months, 61st month shows values red colored and in minus range. So you need to take up to “0” in the balance column. Ignore the values in RED. If you want to calculate for more years, then just select the last row and drag it to extend it.
Download the Excel EMI Calculator.
No related posts.
\\ tags: Bank EMI, EMI Calculator, EMI Excel Sheet, Loan EMI
I have installed Ubuntu on Windows XP just to see how the new version is looking and try some features. At the end of the day, I have uninstalled it, but struck with the problem of bootloader. After installing Ubuntu, it has made changes to Windows bootloader so that while booting we can select Windows XP or Ubuntu from the list. Though I uninstalled the Ubuntu, the bootloader continued to show both operating systems and it was really annoying.
Today, I transferred approximately 200 music files into my mobile phone, but when I tried to play them, only 30-35 files were showing up in the media player. After a quick check, I found that the player in my mobile phone can only play WAV and MP3 files. When I looked at the files, they were mostly in FLV format. So, quickly done a search on the Google and found this tiny tool, which is just 500KB. It is really an amazing tiny application, which took less than half a minute to install and only few seconds to convert all the 170 FLV files into MP3. Quality remained intact.
This week I have worked on a project, which required me to categorize few websites and show screenshots of those websites arranged neatly. Special requirement was dynamic creation of website screenshots because if the administrator wants, he will just add the URL and the screenshot need to be generated automatically.
As per 
Today, I downloaded a file that was having the file extension of DAA. I do not know what DAA is and a quick search on Google told me that it is a 
Recent Comments