sujittalukde

10-26-2010, 12:38 AM

I want a formula which will calculate interest on deposits.

The rule for Interest calculation is given below:

Interest on amount deposited in a month is calculated for lowest balance between 5th day and last day of the month.

Thus for a month say Oct 2010, the balance as on 30th Sep., 2010 will the the opening balance. For any deposit/withdrawal in Oct 2010, the balance as on 5th day of Oct 2010 and 31st day of Oct., 2010 will be added to Opening Balance and interest will be calculated for Oct 2010

BALANCE = Opening Balance + Deposits - Withdrawals.

(For First transaction, BALANCE = Deposits – Withdrawals)

Rate of interest is 8% pa.

I have provided some sample data and the interest calculation is dome manually and correct. I want to do the same by formula.

For deposit made on 07/Mar/2007, No interest will be paid for Mar 10 since balance as on 5th Mar was 0 (Zero) and on 31st March, it is 500/-. So the lease amount is 0 (Zero).

There is no transaction from 01/April/2007 to 09/June/2007 So for April and May 2007, Balance is 500 and interest for Apr 07 is 3.33 (500*.08/12) and May 07 is 3.33 (500*.08/12)

Similarly the deposit on 09/June/2007 will come for calculation for June 07 since 5th day balance is 500 and 30th June balance is (3000+500) 3500. So lease is 500. So for June 07 balance is 3.33 (500*0.08/12)

So interest till June transaction is (3.33+3.33+3.33) = 9.99

However for July 2007, Interest will be calculated on 3500. and so on.

Though interest is calculated on this manner in months but credited in account only on 31st March.

Experts may please help.

Sample file is attached.

Also posted here (http://www.excelforum.com/excel-general/750567-calculate-interest-on-monthly-values.html). but since not getting reply, so posted here for help.

The rule for Interest calculation is given below:

Interest on amount deposited in a month is calculated for lowest balance between 5th day and last day of the month.

Thus for a month say Oct 2010, the balance as on 30th Sep., 2010 will the the opening balance. For any deposit/withdrawal in Oct 2010, the balance as on 5th day of Oct 2010 and 31st day of Oct., 2010 will be added to Opening Balance and interest will be calculated for Oct 2010

BALANCE = Opening Balance + Deposits - Withdrawals.

(For First transaction, BALANCE = Deposits – Withdrawals)

Rate of interest is 8% pa.

I have provided some sample data and the interest calculation is dome manually and correct. I want to do the same by formula.

For deposit made on 07/Mar/2007, No interest will be paid for Mar 10 since balance as on 5th Mar was 0 (Zero) and on 31st March, it is 500/-. So the lease amount is 0 (Zero).

There is no transaction from 01/April/2007 to 09/June/2007 So for April and May 2007, Balance is 500 and interest for Apr 07 is 3.33 (500*.08/12) and May 07 is 3.33 (500*.08/12)

Similarly the deposit on 09/June/2007 will come for calculation for June 07 since 5th day balance is 500 and 30th June balance is (3000+500) 3500. So lease is 500. So for June 07 balance is 3.33 (500*0.08/12)

So interest till June transaction is (3.33+3.33+3.33) = 9.99

However for July 2007, Interest will be calculated on 3500. and so on.

Though interest is calculated on this manner in months but credited in account only on 31st March.

Experts may please help.

Sample file is attached.

Also posted here (http://www.excelforum.com/excel-general/750567-calculate-interest-on-monthly-values.html). but since not getting reply, so posted here for help.