EMI Calculation is a rather complex math with the denominator being the integration of a series (or something of that sort). It is fairly complex to take a pen and paper and calculate. Well, that is if you do not have access to Excel or a similar spreadsheet. In a spreadsheet, it is a very straight-forward calculation to arrive at an EMI.
In this article am explaining how to calculate EMI and amortization schedule of your loan using MS Excel. Also, you can see by changing the EMI how your tenure changes. I’ve attached a sample sheet here with the calculation.
This is the EMI formula in Excel
D4 has the per annum interest
D6 has the tenure in months
D3 has the loan amount.
So what if your loan is quarterly diminishing (or compounding, as they say) even though you are paying monthly. In that case the formula changes a bit.
D6 has the tenure in quarters (i.e. # of years multiplied by 4)
D3 and D4 remain unchanged.
Note the interest changed from D4/12 to D4/4 and tenure from Years * 12 to Years * 4. Also, there is a division by 3 at the end to make it monthly. Similarly you can extend it to any compounding interval.
With slight changes you can use this to calculate the return of a SIP, assuming an average return over the tenure you plan to invest.
The attached sheet has more than just the EMI formula. It’ll generate the amortization schedule for you, given a tenure so that you can play with the EMI amount to reach at a preferred tenure.
Update: Came to know later that Microsoft has similar template for amortization schedule and loan analysis. You can get it here.