Calculating compound interest

[PatJennings]

I have a column of years and a column of numbers representing annual
amounts placed in a savings account for the year. I would like to calculate
the balance of interest earned added to the balance of the account and then
calculate the interest earned on the accumulating amounts each year. The
results would be the account balance displayed in an adjoining column. So
far, I have not found a worksheet function for that. Could someone point me
in the right direction? Perhaps there should be several columns of data?
Thanks

 

[Bernie Deitrick]

Pat,

Your question is a bit unclear. What does your data table look like? Does it have interest earned
in the second column, or total balances?

Bernie
MS Excel MVP

 

[PatJennings]

Actually, it is more complicated than that which I stated in the question.
Let me try to give a better explanation. The problem involves the age-old
dilemma of buying low cost insurance with an escalating premium cost rather
than a fixed-price, higher cost premium and investing the difference between
the two. At some point, the initially low-cost insurance premium becomes
more costly than the fixed-price premium. At that point, the "earnings" or
balance in the "invested account" would be used to offset the higher cost of
premium. Also, since there is no longer a difference to invest, no
additional money can be added to the account.

For example, assume for argument's sake that the cost of the low premium is
$100 and the cost for the fixed price premium is $500. In the first year,
there would be $400 to invest. For the second year the cost would be $110
and $500; third year $120 and $500. Each year the difference in premiums
would be added to the investing account and, for simplicity, earn interest
at, let's say 4% per year. At some point in time the originally priced $100
will increase and become greater than the $500. At that point, the investing
account would have to supply the difference. Interest would continue to be
earned on the account balance.

My columns are:
Low Cost, Fixed Cost, Difference, Interest, and Account Balance, which is
the sum of the preceding two columns

I would like to provide for the eventualities stated in the first paragraph.

Hope this makes it clearer. Thank you for any help you may offer.
Pat

 

[PatJennings]

Actually, it is more complicated than that which I stated in the question.
Let me try to give a better explanation. The problem involves the age-old
dilemma of buying low cost insurance with an escalating premium cost
rather than a fixed-price, higher cost premium and investing the
difference between the two. At some point, the initially low-cost
insurance premium becomes more costly than the fixed-price premium. At
that point, the "earnings" or balance in the "invested account" would be
used to offset the higher cost of premium. Also, since there is no longer
a difference to invest, no additional money can be added to the account.

For example, assume for argument's sake that the cost of the low premium
is $100 and the cost for the fixed price premium is $500. In the first
year, there would be $400 to invest. For the second year the cost would be
$110 and $500; third year $120 and $500. Each year the difference in
premiums would be added to the investing account and, for simplicity, earn
interest at, let's say 4% per year. At some point in time the originally
priced $100 will increase and become greater than the $500. At that point,
the investing account would have to supply the difference. Interest would
continue to be earned on the account balance.

My columns are:
Low Cost, Fixed Cost, Difference, Interest, and Account Balance, which is
the sum of the preceding two columns

I would like to provide for the eventualities stated in the first
paragraph.

Hope this makes it clearer. Thank you for any help you may offer.
Pat

 

[Bernie Deitrick]

Pat,

I have assumed any cost of the variable rate poilicy above the fixed cost policy comes out of the
savings account pricnicpal, interest, or both.

Put your headings in cells A1:E1, then is A2:E2 put these values/formulas:

A2 100
B2 500
C2 =B2-A2
D2 =IF(ISNUMBER(E1),E1,0)*0.04
E2 =IF(ISNUMBER(E1),E1,0)+C2+D2

Then copy B2:E2 down as far as you need, and put in your escalating premiums into column A.

Note that the 0.04 could be a cell reference, if you expect that the interest rates will change.
Perhaps:

=IF(ISNUMBER(E1),E1,0)*F2

and then enter the interest rate assumptions in column F, as percentages...

HTH,
Bernie
MS Excel MVP

 

[PatJennings]

Bernie,
Your assumption in your first sentence is correct.
However, I'm confused as to the contents of columns D and E: the crux of you
solution.
Here are my column headings:
A: Variable rate premium (escalating)
B: Fixed rate premium (constant)
C: Difference between the Fixed and Variable premium
D: Number? times the interest rate
E: Number? plus the values in C and D
It looks like D is calculating a number from a value in E1, which I believe
is column heading.
E appears to be saying if there is a value in E1 add it to the values of C2
and D2, etc.
I'm lost. I can't figure out what is the title of columns D and E.
I appreciate your assistance, and look forward to your helping me understand
your solution.
Sincerely,
Pat

 

[Bernie Deitrick]

Pat,

Normally, you need to have two different formulas, one for the first row (where it is the first
value), and one for the second row and down, after your table has started. I just wrote it so that
one formula would work. TRY setting up the formulas as I described - they do exactly what you want.

These were the titles you listed, that I used in setting up my table:

A1 Low Cost
B1 Fixed Cost
C1 Difference
D1 Interest
E1 Account Balance


HTH,
Bernie
MS Excel MVP

 

[PatJennings]

Thank you Bernie, I finally understood your calculation.
Pat

 

[Bernie Deitrick]

You're quite welcome....

Bernie
MS Excel MVP