Wednesday, December 31, 2008

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
























AB
1Loan Amount1000000
2Loan Interest Rate10.5%
3Loan Tenure(Months)240
4EMI=$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!

7 comments:

serdarb said...

very nice post...

Sayali said...

please explain the formula...

sankalp said...

nice work done buddy.
i want to calculate monthly reducing principle amount, interest ???? can u help me out!!!!

Anonymous said...

The calculation was really helpful. Websites showed only autoomated calculation..not on how its calculated...your formule on excel is easy to understand...thanks a ton..

Anonymous said...

hi

Anonymous said...

Hi I want to create an EMI Table in KSH..please let me know the formula

thanks

Stephanie6 said...

Well written-This post is equally informative such as interesting.Thanks for information you been putting on making your site such an Wesome If you have time please visit my emc testing lab page.