Home loan overdraft calculation sheet

Okay, so blogger does not allow uploading attaching files but google docs does, its a cool new feature I think. 

Here you go. The excel sheet can be found here. The copy of the description from gharonda email is as below:

It handles max of 20 years of loan calculation. Does the EMI, pre-EMI calculation for you. For simplicity sake I am assuming the deposit is made on 1st, else one can calculate and put the number.

For basic users, please fill in the sheet with yellow color cells, rest will be calculated automatically.

For others who want to put in different amounts in different months can modify the blue colored cells.

In the given sheet a sample is already filled in, it shows that along with EMI of Rs. 9651 if we pay only 500 extra in this account for a 20 year loan of Rs. 10 lakhs, the interest saving is 2.15 lakhs, please note that at no point of time did we actually maintain a balance of more than 1 lakh (approx). After 207 months in this example, you can stop paying EMIs as it will be taken care by the savings you would have put in this account.

This sheet can be further modified to calculate interest for interest rate changes and part disbursal. Let me know if anyone needs these options.

The few things to try here are:

1) If you just put your monthly savings every month, how does it look. (Remember your saving is working hard for you)

2) If you just put your annual bonus into this account, how does it look. (Its better than pre-payment, as the amount is not gone away, if you need you can withdraw)

To take max advantage of this loan I recommend:

1) Take max tenure possible

2) Take max amount of loan possible, put the rest in this account and you have a ready emergency fund which works for you.

I call this account "magic" account :-). It’s a magic account for people who are disciplined, if you put some money and keep on removing it every month, then it does not get you that good a benefit.

 

Loan Amount	1,000,000.00
Interest	10.00%
Tenure	20.00
EMI	9651.00
Start Date	40200.00
Pre EMI	2740.00
Total interest	1316240.00
Net Monthly Addition	500.00

 

Effective Tenure	207.00
Effective Interest	1,101,606.82
Interest Saving	214,633.18

Cheers!