Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I calculate interest?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I calculate interest?
"darkwing_duck" wrote:
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 ----- "darkwing_duck" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to calculate interest on payments | Excel Discussion (Misc queries) | |||
How do you calculate an interest only loan | Excel Discussion (Misc queries) | |||
Calculate Daily Interest | Excel Discussion (Misc queries) | |||
How to calculate Compound Interest | Excel Worksheet Functions | |||
calculate interest between two dates | Excel Worksheet Functions |