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

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" wrote in message
...
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

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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

"PatJennings" wrote in message
...
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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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,
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

Thank you Bernie, I finally understood your calculation.
Pat
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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 a
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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

You're quite welcome....

Bernie
MS Excel MVP

Thank you Bernie, I finally understood your calculation.