monthly compound interest - is there a better way?

[Michael J. Gould]

A subject like this came up about a year ago but was deleted from the server
(only the title remains.)

I am laboriously building a horizontal cell line, the first cell (A1) being
the principal amount, the second, third, fourth etc. being the compounded
interest.

A1= n (principal amount); B1=A1*.02; C1 = (A1+B1)*.02; D1= (A1+B1+C1)*.02,
etc.

The problem is, there is no way I can see to drag the formula horizontally
from B1 or C1 all the way to (say) Z1. We are manually inputting the
formula into each cell in the first row for (in this case) 42 cells.

Is there a better way?

 

[Norman Harker]

Hi Michael!

Try one way:

=SUM($A1:A1)*0.02
Copy across

 

[Norman Harker]

Hi Michael!

A much more complicated way but one that shows what you are doing in
Time Value of Money terms:

-(FV(2%,COUNT($A1:B1)-1,0,$A1,0)-FV(2%,COUNT($A1:A1)-1,0,$A$1,0))
Copy across

You're calculating the difference in the accumulated balance between
period n and period n-1

Probably other ways of doing it but it does show what you are doing
and has the possible advantage of being independent of entries in
preceding cells (other than A1). You might also obtain your count from
period numbers.