I was asked how EMI interest and principal works – the idea of that is kinda complex, like the calculation describes. But the essential funda is: Take the total amount payable, pay the interest first and the remaining part of your EMI is the principal. You can calculate this easily using spreadsheet functions like PMT, PPMT and IPMT. I have demonstrated this, on a monthly basis, by using this excel sheet:

This is just for 12 months but you can extend it for any number of months.

You can find out exactly how much you’ve repaid at any month and how much is left, and of course, you see how your interest is front loaded on any such loan.

You can view the spreadsheet or download in Excel format.

*Related*

hariApril 5, 2007 at 6:58 AM>HI Deepak,

I have a query regarding the midcap funds that one can invest in.I thought of mailing you but did not get the time.So I am posting it here. My concern is that many good performing mid caps like the Sundaram Select Mid Cap,SBI magnum global, and Reliance Growth have exceedingly large corpusses. Since the Mid Cap companies have a market cap of only 500 crores ,will investing in these funds prove fruitful. I was seeing the AUM for these Mid cap funds,and have found that only Birla Mid Cap fund has a manageable corpus (226 crores) in the mid cap space. In such a scenario is it worthwhile to invest in Birla Mid Cap fund. I still have not seen the AUM of DSP Tiger fund. Wount the high AUM be a drag on the performance? Please give your views on the same.

Thanks

Regards

Hari

AlienApril 5, 2007 at 9:11 AM>Thanks Deepak..

Luckily I got kind of inspired with your mails and worked it out myself.. your post just helped me to cross check my calculation and show I was right… I am trying to extend it a bit by incorporating income for a year and taxes (at present rates) so that I can calculate the effect on cash out flows on a yearly basis…

Just a question.. Is there any statistical data / reference that allows one to look at inflation, real estate prices over the last few years and how rents have changed so that one can incorporate that into the calculation to make it more realistic? I remember you doing som e calculation like that once and coming to the conclusion that capital appreciation is the reason one should buy real estate…

I want to check the feasibility angle… how large a loan will I be able to afford?

PS: also some information on how salaries have changed over the last few years

T.S.K.PrabhuApril 6, 2007 at 11:41 AM>Hi,

http://www.apnaloan.com gives you a better loan calculator.It shows the principal and interest component seperately for each month.

Regards

PRABHU

Deepak ShenoyApril 6, 2007 at 11:57 AM>Prabhu: That’s what mine does too. The interest and principal components for each month are shown.

AnonymousApril 10, 2007 at 4:34 PM>Hi Deepak,

Microsoft Excel gives the readymade calculator. Just search for Loan Amortization.xlt on your hard drive. This excel template has exactly the same thing readiely available with some additional features like monthly additional extra principal repayment.

Deepak ShenoyApril 10, 2007 at 5:23 PM>Awesome! I didn’t know that existed. Really cool stuff. Gives me a lot of ideas now :)

AnonymousApril 11, 2007 at 3:42 PM>The Loan Amortization.xlt is excellent. Could someone point to more of such ready made excel sheets

Cheers

Vinayaka CA

AnonymousApril 16, 2007 at 5:21 PM>Hi,

I am bit confused with the ‘ToRepay ‘ column of your example of 100,000.00 at rate 18% for 12 months.

Here I feel IPMT is giving wrong results. Who will repay ‘89,984.01’ after 12 months tenure (,which is present at the end of excel column ‘ToRepay’). Even I had the same problem.. I dont know how to solve.

I vae cross verified with my bank detail with the spread sheet. It is totally different except EMI (PMT).. So PMT calculation are right. But not interest IPMT & PPMT.

Please clarify…

Deepak ShenoyApril 16, 2007 at 5:53 PM>I think you are using the wrong interest %. What’s your bank telling you in the Principal/Interest payments for the first month?

AnonymousMay 8, 2007 at 6:35 AM>Hi Deepak. Thanks for this excellent worksheet. Just one observation.I think there is an error in the formula in Column ‘G’. It doesnt give the ‘to repay’ as zero at the end of the tenure. I just changed the column in the formula. Works fine now. Pls correct. Thanks again – Ranjan

Deepak ShenoyMay 8, 2007 at 12:11 PM>Thanks Ranjan! Corrected now.

AnonymousNovember 11, 2008 at 8:24 AM>May be you would like to look at this,

http://cpmlive.wordpress.com/2008/11/08/advance_loan_amortization_emi_calc/

tagMacherJanuary 10, 2009 at 5:00 AM>Terms such as ‘daily reducing”, “Monthly reducing” etc. are used by banks and other lending institutions. I need to compare between these. How do I modify the calculation for this?

RiteshAugust 6, 2010 at 9:49 AM>Hi Deepak Shenoy its great to find this but i had a little confusion i had recently taken a loan of Rs. 200000/- @ 12% for 24 months and the EMI that i am paying to my bank is Rs. 10333.33, but on coming across your formula when tried the same the EMI comes to Rs. 9414.69. Please let me know the difference

MaheshJune 26, 2011 at 4:20 PMI searched for the Loam Amortisation.XLT in my system, its not available. Which version of XL does it contain. I’m using 2010.

vishwaroopMarch 28, 2013 at 1:25 PMinterest and principal chart for structured IRR ex loan amount rs.600000 with one emi advance flat rate will be 9.75% and the IRR for 4 years works out to 24.42%. the contract value will be rs.834000

1st year emi will be rs.24325 ( 35% of the contract value )

2nd year emi will be rs.20850 ( 30% of the contract value )

3nd year emi will be rs.13900 ( 20% of the contract value )

4th year emi will be rs.10425 ( 15% of the contract value )

kindly mail the excel sheet with the working of the interest principal chart

Your comment is awaiting moderation.