EMI calculation formula for spreadsheet

Nowadays everyone seems to be taking loans for varying needs such as home, automobile, education etc. We start paying the EMIs per month to pay back the loan as per the bank mandate. Some of us, are tech savvy or nerdy enough to want to keep track of the loan payment by wanting to create an excel sheet for the same. Some time we may want to create an Excel for our monthly EMIs maybe just to calculate how much are we paying in interest and principle per month.

I had to fumble with the EMI formula to make it work in Excel, so here I am sharing the Excel/Google spreadsheet formula so you may use it in your own loan amortization sheet right away!

Use the formula shown below to calculate the EMI for your Loan. The table below shows dummy values, which should result in an EMI of 9983.80

	A	B
1	Loan Amount	1000000
2	Loan Interest Rate	10.5%
3	Loan Tenure(Months)	240
4	EMI	=$B$1*(B2/12)*(((1+(B2/12))^$B$3)/(((1+(B2/12))^$B$3)-1))

I hope this comes handy to you. Do let me know by commenting if yuo have anything to add or you used this article. Wishing you a Happy New Year!