Calculate Optimum Duration in Loan

[anuradhagrewal]

Hello everyone
I am trying to figure out this loan calculation. I have attached the excel file as attachment: OD Payment Calculator. xlsx
Actually I taken a loan on a Fixed Deposit(FD) (Term Deposit) from my bank.
Such a loan is known as a Over Draft.
The bank is charging me 2% more than the interest they are giving me.
For eg: If they are giving me 7% interest for a 36 month(3 year) FD I can take up to 90% amount as a OD but the interest charged by the bank will be 2% higher or 9%.
As I get my interest monthly the OD interest will also be monthly.
I have made this excel sheet to that effect.

Now the bank does not expect me to pay any fixed amount every month but I want to figure out what is the best installment I can pay every month.
I am unable to figure that.

I need the help of you wonderful experts in figuring out that

1. As you all can see column E is negative till cell E52 where it turns positive. If there is someway I can get on the sheet in a cell where it states that in how many months will I be able to pay off the OD amount. If that month or the row can be highlighted by conditional formatting as green would be great.
2. I have manually added 55 rows as the FD period is 55 months which in turn is the OD repayment period too. If somehow this addition and deletion of rows can be linked to the figure depicted in Cell K4 (which is basically duration).
3. I also seek your help in automatically calculating the total of column H and the total of column I to the date when Column E turns positive.

For eg :In the current scenario E52 is when the OD amount turns positive which means that I have repaid the entire amount of Rs 54000 that I had taken as a OD. Correspondingly if I sum up column I from H7 to H51 I received interest of Rs. 16125 and similarly paid an interest of Rs 9887 when I sum up column I 7 to I 51.

Please guide me in this calculation.

It will be of great help.

Thanks

Anuradha

OD Payment Calculator. xlsx

 

[anuradhagrewal]

Please help somebody