Sunday 30 January 2011

Maturity amount calculation for recurring deposit in MS Excel

Maturity amount calculation for recurring deposit where interest is compounded quarterly, using the function FV:

Maturity amount = FV( (Rate of interest)/4, 4 * (Period in years), - (Value of each instlament) * (3 + (Rate of interest) / 2))

For example, an RD of Rs. 5000 each month for a period of 1.5 years at interest 7.5% p.a. compounded quarterly yields on maturity and amount of:

FV(7.5%/4, 4 * 1.5, -5000 * (3 + (7.5%/2))) = Rs. 95,504.78

The above formula is a general formula and individual banks might use a slightly different formula.  For example, the Indian Banks' Association uses the following formula for computing the maturity value where interest is compounded quarterly (Source: http://www.iba.org.in/formula.asp):


Maturity amount = ((Value of each instlament) * ((1+i)^n-1))/(1-(1+i)^(-1/3)),

where i = (Rate of interest) /4

    and n = number of quarters.

Hence, taking the same example of RD of Rs. 5000 each month for a period of 1.5 years at interest 7.5% p.a. compounded quarterly, the maturity value, according to this formula is:


((5000) * ((1+(7.5%/4))^6-1))/(1-(1+(7.5%/4))^(-1/3)) = Rs. 95502.35

Extending the formula to other compounding periods:


For Monthly compounding: 

Maturity amount = ((Value of each instlament) * ((1+i)^n-1))/(1-(1+i)^(-1))

where i = (Rate of interest)/12

   and n = number of months.


For half-yearly compounding: 
Maturity amount = ((Value of each instlament) * ((1+i)^n-1))/(1-(1+i)^(-1/6))

where i = (Rate of interest)/2

   and n = number of years * 2.


Download the following app on your android phone to compute the maturity amount for fixed and recurring deposits: http://goo.gl/olkbN.  You can also do these calculations online at https://bank-interest.multisocialshare.com/

13 comments :

smartmoneyindia said...

Hi..

I think you have to change your formula

Round(FV(7.5%/4, 4 * 1.5, -5000 * (3 + 7.5%/2)),-2)

answer is = 95,500..

Thanks

Nikhil Shah

E-mail Id : [email protected]

Keshavaprasad B S said...

Hi smartmoneyindia,

THe only difference I see in the formula you suggested is the addition of the round function. Am I right?

Thanks,
Keshavaprasad B S

munish sharma said...

thanks man...

మన జీవనం said...

Will you pl explain why you used 3+ in the formula

[email protected]

VP said...

Hello,
What change is required in the formula if we need to calculate compound half yearly

Keshavaprasad B S said...

The formula would be:

Maturity amount = FV( (Rate of interest)/2, 2 * (Period in years), - (Value of each instlament) * (6 + (Rate of interest) / 2))

to calculate compound half yearly.

Keshavaprasad B S said...

We are calculating the maturity amount compounded quarterly in that formula. Each quarter has three months. The third argument in the formula needs the payment done per compounding period. Hence using 3+ there.

Somendu said...

Hi,

What would be the formula for yearly calculation

I am getting some mistake
Please rectify

Is this correct??

Maturity amount = ((Value of each instalment) * ((1+i)^n-1))/(1-(1+i)^(-1/12))

Keshavaprasad B S said...

yes, according to the IBA formula, it is:

((Value of each instlament) * ((1+i)^n-1))/(1-(1+i)^(-1/12)),

where n is the number of years,
and i is the rate of interest.

For example, for an amount of Rs. 10,000, for a period of 5 years at a rate of 10 percent interest, the maturity amount would be:

((10000) * ((1+10%)^5-1))/(1-(1+10%)^(-1/12)) = Rs. 7,71,717.40

Unknown said...

Can You please tell me the formula to calculate Rate of interest compounded quarterly for Recurring deposit if all other parameters are known.

Keshavaprasad B S said...

As mentioned in the blog, it is:

((Value of each installment) * ((1+i)^n-1))/(1-(1+i)^(-1/3))

where i is (rate of interest / 4), and n is number of quarters (number of years * 4) for which the deposit is made.

For example, for maturity amount for RD of Rs. 5000 per installment for a period of 1.5 years at interest 7.5% p.a. compounded quarterly,

i = 7.5%/4 = 1.875%
n = 1.5 * 4 = 6
Maturity amount = ((Value of each installment) * ((1+i)^n-1))/(1-(1+i)^(-1/3))
= ((Rs. 5000) * ((1+1.875%)^6-1))/(1-(1+1.875%)^(-1/3))
= Rs. 95502.3508376

Unknown said...

This formula can be used to calculate maturity value but I want the formula(to be used in Excel) to calculate the rate of interest if maturity value is known in case of RD.

Patel Software said...

THANK A LOT
I AM SEARCHING IT, I GOT IT HERE.
THANKS AGAIN.
As this is useful to my Tally.ERP (Accounting Software) customization tdl programme.
-Hasmukh Patel
www.tallyplus.in