How do I calculate interest?

[darkwing_duck]

I want to create a spreadsheet that shows my kids the power of
compounded interest. I want to show them what regular deposits
against a specific monthly interest rate results in, at the end of the
year. I want to have the interest rate in cell A1 and starting in row
5, in column A I have the date of deposit and in column B I have the
deposited amount. Then I want to have a total that calculates the
updated balance including the compounded interest rate each month.

Any suggestions? TIA!
Robert

 

[Joe User]

I want to show them what regular deposits against a
specific monthly interest rate results in, at the end
of the year.

Caveat: the numbers might not be very impressive over such a short period
of time. But that depends on the age of the kids.

Also note that interest rates are usually expressed an annual rate. I
suggest that you do the same, since that is what the kids will be exposed to
in real life.

I want to have the interest rate in cell A1 and starting
in row 5, in column A I have the date of deposit and in
column B I have the deposited amount. Then I want to
have a total that calculates the updated balance including
the compounded interest rate each month.

Since you said "regular deposits" (i.e. occurring a regular intervals), the
dates do not need to be used in the calculation, especially since accuracy
is not that important in this case.

Suppose A1 is the __annual__ interest rate.

A5, initial date: 3/1/2010
B5, regular deposit: 123
C5, ending monthly balance: =SUM(C4,B5)*(1+$A$1/12)
A6, next date: =EDATE($A$5,ROW()-ROW($A$5))

Copy B5:C5 to B6:C6. Copy A6:C6 down through A16:C16.

Notes:
1. This assumes that C4 is empty or text.
2. SUM(C4,B5) instead of C4+B5 allows for C4 to contain text (e.g. column
header).
3. EDATE() instead of DATE(YEAR(A5),1+MONTH(A5),DAY(A5)) does the right
thing when the initial date is the 29th through 31st, days that some later
months might not have.
4. For most savings accounts, interest is usually compounded daily, not
monthly. Probably, that would not make any noticable difference, especially
if you use small amounts for the kids.
5. Note that C5 is the balance of the __next__ date (C6), not A5. That
seems awkward. If you want help to fix that, post again.


----- original message -----