## 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/

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)

Thanks

Nikhil Shah

E-mail Id : [email protected]

Hi smartmoneyindia,

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

Thanks,

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

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.

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

Is this correct??

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

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.

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