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.
Post a Comment