[Q] How to use Excel to show est. compounded value displayed by mo

[JohnH]

I have two years to save
I have a starting value, say $100
I have an intrest rate, say 2%
I want to add $10 a month

[Q] How can I use Excel to show my total compounded balance 'each month'
forecast over the two years ?

pmail also please to (e-mail address removed)

Thanks, J

 

[Joe User]

I have two years to save
I have a starting value, say $100
I have an intrest rate, say 2%
I want to add $10 a month
[Q] How can I use Excel to show my total compounded balance
'each month' forecast over the two years ?

Depends on what you mean.

1. You mean that you invest $100 at the beginning of the first month and $10
at the beginning of each of the remaining 23 months.

Total at the end of 24 months:
=FV(2%/12,23,-10,-100*(1+2%/12),1)

A1: =100*(1+2%/12)
A2: =(A1+10)*(1+2%/12)

Copy A2 down through A24.


2. You mean that you invest $100 at the beginning of the first month, and
you add $10 at the end of each of 24 months including the first one. Thus,
you do not earn interest on the last $10 deposit.

Total at the end of 24 months:
=FV(2%/12,2*12,-10,-100)

A1: =100*(1+2%/12)+10
A2: =A1*(1+2%/12)+10

Copy A2 down through A24.


3. You mean that you invest $100 at the beginning of the first month, and
you $10 at the beginning of the each of 24 months including the first one.
Thus, you invest $110 initially(!).

Total at the end of 24 months:
=FV(2%/12,24,-10,-100,1)

A1: =110*(1+2%/12)
A2: =(A1+10)*(1+2%/12)

Copy A2 down through A24.


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

I have two years to save
I have a starting value, say $100
I have an intrest rate, say 2%
I want to add $10 a month

[Q] How can I use Excel to show my total compounded balance 'each month'
forecast over the two years ?

pmail also please to (e-mail address removed)

Thanks, J

 

[Fred Smith]

You set up a table.

A2 is a period or date, say, 2009-12-01
B2 is the opening balance, $100
C2 is your payment, $10
D2 is the interest earned for the period, $0
E2 is the closing balance, B2+C2+D2
A3 =date(year(a2),month(a2)+1,day(a2)
B3 =E2
C3 =C2
D3 =E2*2%/12
E3 =B3+C3+D3

Copy row 3 down as far as necessary.

Regards,
Fred