Maturity Amount for Recurring Deposits

all4excel · 05-17-2008, 08:10 AM

Maturity Amount for Recurring Deposits

Hi!

I want to find the formula for getting the Maturity Amount for a recurring deposit.

My bank debits my account for $100 every month and I have this arrangement for 12 months then what would be my Maturity amount?

I know the Interest rate - 3% p.a compounded quarterly.

How can I get the total amount alongwith the interest for periodic payments for a given period?

for example I am paying $100/- per month for 12 months and the bank is offering interest rate of 3% per annum.

What amount i would get from the bank at the end of 12th month?

I need the simple formula.
Analogy :
Like Profit = Selling Price - Cost Price.

and then something in Excel...

**davesexcel** · 05-17-2008, 09:05 AM

=FV(rate,nper,pmt,pv,type)

is the future value formula

all4excel · 05-17-2008, 10:58 AM

Originally Posted by davesexcel

=FV(rate,nper,pmt,pv,type)

is the future value formula

Thanks but I am not able to understand this completely..

This is the information in help..

FV(rate,nper,pmt,pv,type)

For a more complete description of the arguments in FV and for more information on annuity functions, see PV.

Rate is the interest rate per period.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.

Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.

Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Set type equal to If payments are due
0 At the end of the period
1 At the beginning of the period

By using the above formula,
[ =FV(rate,nper,pmt,pv,type) ]

[ =FV(3%,12,100,100,0) ]

Can u please provide an explantion if you could as I want to start with different Recurring Deposits.?

all4excel · 05-17-2008, 11:10 AM

I am not an expert in Finance but I surely know what I want..!

I would be glad if this is possible as I would have the following information :
1. Start dates and End Dates of these Recurring Deposits. B2 and C2.
2. The rate of interest offered by the Financial Institution D2
3. The Amount. E2
4. The Interest is Compounded Quarterly- F2

Just to add, the first installment of $100 would be having 4 quarters in a duration of 1 year, the next amount would however have only three 3 complete quarters and 2 months, the next 3 complete quarters and 1 month, the next 3 complete quarters and 0 months, so on so forth.

Now the number of installments can be explicitly given or can be derived from Start dates and End Dates.

The Dates are fixed and the amount is actually going to be debited on the same day every month from my bank account as per the standing instruction.

The Invested amount would be a summation of all the installments paid to the Financial Institution.

The Maturity Amount needs to be calculated by using the above data by the earlier formula in an Excel fashion.

But Im not able to incorporate the different arguments from the formula.
[ =FV(rate,nper,pmt,pv,type) ]

I know what I want to get but am not able to understand how to use this formula...?

So can someone help me on the same.

all4excel · 05-17-2008, 12:30 PM

Anyone please help..

Bump...

I tried the Excel FV function but it does not give a provision to add the Start Date/End Date or the duration.

It gives the closest result for a known maturity amount but not the exact..

**davesexcel** · 05-17-2008, 01:53 PM

http://www.frickcpa.com/tvom/TVOM_FV_Annuity.asp#excel

has a sample,
as for a start date and end date, you may have to be creative

Microsoft help has some good info on FV

all4excel · 05-17-2008, 03:26 PM

Originally Posted by davesexcel

http://www.frickcpa.com/tvom/TVOM_FV_Annuity.asp#excel

has a sample,
as for a start date and end date, you may have to be creative

Microsoft help has some good info on FV

Dave,

Thanks for your help, however would have been better if I had got some formula or an idea to tweak it to convert it into Excel with Start / End Date as the amount varies depending on the number of days.

all4excel · 05-18-2008, 09:18 AM

Anyone please help me on this as all the formulas are giving approximate results but I want something robust..

[ =FV(rate,nper,pmt,pv,type) ]

This formula does not consider the compounding frequency as well as the actual dates and therefore there would be accuracy missing in the same.

**davesexcel** · 05-18-2008, 11:35 AM

I'll start with this example,
Hopefully from here somebody can get you what you are looking for...

all4excel · 05-18-2008, 12:31 PM

Originally Posted by davesexcel

I'll start with this example,
Hopefully from here somebody can get you what you are looking for...

I am sorrt to say but it does not give the accurate Maturity amount..

I'll give you an example ..

I would be getting $19,912 after 3years- 36 months after investing $500 every month from 9th June 2006 till 22nd may 2009.

Now the Recurring Deposit was opened on 22-May-06 with an amount of $500but the standing instructions to receive installments was 9th of every month..

So, the first installment was made on 9th June 2006.

Now , the total amount invested would be $18,000 @6.5% compounded every quarter..

I am not able to get the same Maturity amount of $19,912..

Can u please explain the same?