Verification of Interest paid by Bank to my account

[BK LAAL]

Hi All

I am trying to verify the interest paid to me by bank in my account

Based on given start date and end date, I wanted to check what could be the interest payable to my account.

Can any one suggest formula for the No of days column in the attached xls.

Note : My objective is that I would be able to check the interest being paid to me in next 6 months assuming that no further transaction will be done in my account, so i have kept the start and end date

 

[Faseeh]

@BK LAAL

The Column J already contains values for interest so what do you want to calculate, secondly can't understand six month concept. thanks.

 

[BK LAAL]

Hi Faseeh

No of days column (column I) has been manually entered. I want formula for that column, so that no of days will be calculated.

secondly. Normally bank pays interest every 6 months, Let say every 1-Jan and 1-july.

The interest for the period 1-Jan to 30-June it will be credited to my account on 1-July.
So now let say on 25-June i want to calculate the expected interest that will be credited to my account on 1-july.
I should be able to do so by giving the required start and end dates.

Hope i am clear enough

 

[Faseeh]

Hi BK,

For days you can drive days out of your formula, try:

=(J6*36500)/(H6*F$2)

 

[BK LAAL]

No, It will not work based on the no of days i ll be able to calculate the interest.

to put in simple words. U have your bank account transaction list. You need to write a tool to verify if the interest credited to your account.

the attached xls is nothing but my bank transaction list, I have just added 2 column, No of Days and Interest.

The interest is caluculated on the daily closing balance. So I need to know no of days the same balance was available

 

[Faseeh]

Lets assume that you had $100 in your bank account. If you are getting 9% per year, you should get 0.09*100/12=$0.75 added in your account for each month. Now the situation is that (Lets assume) 04 months have passed and your account is showing $101. You want to verify is that should be the status? Instead you should have 100+0.75*4 = 103 dollars in your account as an ideal case. Is this description correct?

 

[BK LAAL]

Yes Faseeh

adding to it . In your example you have not taken care of transacton in between

Ideally we need to calculate the no of days the balance remains same

 

[Faseeh]

Hi BK,

Please try this formula

=SUMPRODUCT(((E13-B5:B8)*(F5:F8+(-E5:E8))*F2)/36500)

or see the file below