How to calculate EMIs, Home Loan payments, home affordability, pre-payment of a loan in Microsoft Excel?

Author: Sachin Gupta | Find me on Twitter Follow @sach_gupta

Many a times, we have been inundated with queries such as how to calculate the Equated Monthly Installments (EMIs), what is the formula for checking the home affordability, what amount will I have to pay if I pre pay my home loan? These are basic yet important questions and therefore, understanding these concepts is crucial for real estate investment. Here we present the formulas in Microsoft Excel for you to calculate EMIs, Interest payments, home affordability, pre payment, changing the loan tenure.

Equated Monthly Installments (EMIs):
As the name suggests, EMIs are the monthly payments you will make for loan against property or any other thing.

Here is an example:
Loan Amount (Rs) - 100000
Interest Rate (%) - 11
Loan Tenure (Years) - 20

EMI (Rs) - 1032
Total Interest Payable (Rs) - 147725
Total of Payments (Principal + Interest) (Rs) - 247725

The formula for calculating EMI in excel is given below:
=PMT(rate, nper, pv, [fv], [type])
Rate = Interest Rate in percent, nper=Loan tenure in months, pv=present value or principal amount, fv=future value

During the EMI Calculations, leave out ‘fv’ and ‘type’ and fill in the other values.
=PMT((Interest Rate/12)%, Loan Tenure*12,- Loan Amount)
=PMT((11/12)%, 20*12,- 100000)
EMI = Rs. 1032

In this calculation, we divide interest rate by 12 to arrive at the monthly interest charged.


Interest that is paid on each EMI:
=IPMT(rate, per, nper, pv, [fv], [type])
IPMT – Interest paid for a given EMI
Rate – rate of interest
Per - The month for which you want to find the interest and must be in the range 1 to nper.
Nper- total number of months
Pv – present value or principal amount
Fv- future value
Type- optional

=IPMT((11/12)%, 1, 240, -100000)
=Rs. 916.67 (It means, on your first EMI of Rs 1032, the interest paid will be Rs 916.67)

=IPMT((11/12)%, 240, 240, -100000)
=Rs. 9.38 (It means, on your 240th EMI of Rs 1032, the interest paid will be Rs 9.38)

=IPMT((11/12)%, 200, 240, -100000)
=Rs. 322.15 (It means, on your 200th EMI of Rs 1032, the interest paid will be Rs 322.15)

Similarly, you can calculate for other monthly EMIs by just changing the ‘per’ value from 1 to 240


Total Interest paid during the tenure of the loan:
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
=CUMIPMT((11/12)%, 240, 100000, 1, 240, 0)
=(Rs. 147725.21)

In our example, start period is 1 and end period is 240. You can also calculate the total interest paid say for a period of 13 to 228. In other words, how much interest did you pay from second year on-wards up to the end of 19th year.

=CUMIPMT((11/12)%, 240, 100000, 13, 228, 0)
=(Rs. 136089.79)


Home Affordability:
Home affordability is the measure of the value of the home that you can afford given your current household income. Detailed analysis of home affordability is given here.


Pre Payment or changing the tenure of the loan:
Suppose, you secured a home loan of Rs 100000 in 2008 and have paid 60 EMIs thus far. You have now decided to pre pay your entire loan amount. What will be the value that you will have to pay now? Here is the answer:
=IPMT(rate, per, nper, pv, [fv], [type])/rate
=IPMT((11/12)%, 61, 240, -100000)/ (11/12)%
=(Rs. 90813.93)

Similarly, you can calculate for any period. Say, for example, you have paid 88 EMIs and now want to pre pay the loan amount. Just replace the value of ‘per’ to 89 from 61.
=IPMT((11/12)%, 89, 240, -100000)/ (11/12)%
=(Rs. 84471.24)

Having arrived at the loan balance using the above formula, you can either pre pay the entire balance amount or reduce the tenure of the loan to arrive at new EMIs using the payment formula.
=PMT(rate, nper, pv, [fv], [type])

Thanks

Follow @sach_gupta

Have any Questions? Tweet to @sach_gupta