Expert: Josh - 2/3/2010

Question
If Rs.10,000/= are invested on the first day of each month for 60 months, please compute the total amount (maximum) that it may become at the end of the 60th month. Please show the detailed working. Secondly, try to compute it using EXCEL functions (or in any way to make it simple). Interest is applicable @12% p.a. (State any assumptions that you make). You can try alternative solutions to the problem to try to make it ‘maximum’

Answer
Hello Swati,

There are too many twists in this question for me to contend with. The various suggestions are best left as an exercise and something for you to explore. The basic premise of this question is "compound interest calculation". Rather than developing this idea from scratch, I recommend that you look up "compound interest with periodic payment" on a search engine, to find a tutorial or suitable examples on this very common topic. The information that you will find will be presented in a much nicer form, than what I can manage here by typing text without any maths equation editor or graphics.

The main ideas are outlined below.

Let P=10000 [Rs],
   r=R/12 be the monthly interest (R is calculated per annum),
   A(n) be the amount accumulated at the end of month "n".

Keep an eye out for patterns.

At the end of the first month, (n=1):
A(1)=P+P*r=P(1+r).

At the start of the second month, you deposit P on top of this. The combined total attracts "100*r"% interest. So,
A(2)=[P(1+r)+P]*(1+r)

A(3)=[(P(1+r)+P)*(1+r)+P]*(1+r)
   =[ P(1+r)^2+P(1+r)+P]*(1+r)

A(4)=[(P(1+r)^2+P(1+r)+P)*(1+r)+P]*(1+r)
   =[ P(1+r)^3+P(1+r)^2+P(1+r)+P]*(1+r)

...by induction,
A(n)=P*[(1+r)^(n-1)+(1+r)^(n-2)+....+1]*(1+r)

For simplicity, let z=(1+r). Then, we can write
A(n)=P*[z^(n-1)+z^(n-2)+....+1]*z     --- This is the formula!

The sum inside the square bracket is a geometric series with ratio z=(1+r).
Hence, using the formula [G.S.=a(z^n-1)/(z-1), where "a" is the first term and z>1]
A(n)=P*z*(z^n -1)/(z-1) where z=1+r.

Now, we have found a closed form expression. The hard part is done.

--------------------------
In Excel, you can do this:
Label cell A1 "Start of Month"
Label cell B1 "End of Month"

Enter cell A2 the value of P.
Set formula cell B2 = A2*(1+r), where monthly interest r=R/12=0.01
Set formula cell A3 = B2+P.
Set formula cell B3 = A3*(1+r).

Repeat compound interest and top up calculations n times (for n months).

Separately, calculate P*z*(z^n -1)/(z-1) where z=1+r in cell "C_{n+1}" to verify the validity of the formula.