PDA

View Full Version : Calculate Interest on monthly values

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.

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.

xld
10-26-2010, 01:49 AM
Try

=IF(ROW(B2)=2,0,ROUND(IF(DAY(B2)>5,F1*8%/12*DATEDIF(B1-DAY(B1)+1,B2-DAY(B2)+1,"M")),2))

sujittalukde
10-26-2010, 03:27 AM
Thank you xld, for the reply. However it has certain bugs.
1. If the first date 7/Mar/2007 is changed to 3/Mar/2007, Interest is coming to 0 (Zero), which should be (500 * .08/12) as the amount is deposited before 5th day of Mar 07. So the amount for inetrest calculation as on 31/Mar/07 is lowest of Balance as on 05th day and as on last day of Mar 07. If the date of deposit is 3/Mar/07, then balance as on 5/Mar/07 is 500 & since there is no Withdrawal, balance as on 31/Mar/07 is 500. So the Lease amount is 500 and interest is calcualted on 500.

2. The same case to other transaction dates. If the date is reduced to 5th day or below, interest is coming to zero.

xld
10-26-2010, 05:10 AM
Okay, so assuming the first date is 3rd March, what is the interest calculation for the second period?

And what if the first period is 3rd March and the second is 3rd June?