Compound Interest

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

Hi.

As a bit of a novice, I need help with a formula for displaying compound
interest over a number of years. For instance...

Calculate the final value of an investment of £100,000, each year earning 5%
compound interest.

If, beginning in cell B2 I enter 5% and in C2 I enter the opening sum
(£100,000), what formula would I use to obtain 10 years total value with
compounded interest in column D2? I will need each year's value shown. (D2
to D12 - I think)!

Please be gentle with me, I'm not too clever with Excel!


Thanks in advance.

Ed





--
NOTE: This message and any attachments thereto is confidential and for the
benefit of the addressee only. Any other person reading, using the
information therein provided or passing this information or any attachments
thereto on to any other party, will be in breech of copyright and may be
pursued through the courts.
 
"Edward" wrote ...
Calculate the final value of an investment of £100,000, each year earning 5%
compound interest.

If, beginning in cell B2 I enter 5% and in C2 I enter the opening sum
(£100,000), what formula would I use to obtain 10 years total value with
compounded interest in column D2? I will need each year's value shown. (D2
to D12 - I think)!

--
NOTE: This message and any attachments thereto is confidential and for the
benefit of the addressee only. Any other person reading, using the
information therein provided or passing this information or any attachments
thereto on to any other party, will be in breech of copyright and may be
pursued through the courts.
Click to expand...

Hi Edward,

Try =-FV(B2,10,,100000) where B2 contains 5% and C2 contains 10 years.

You can enter the formula in any cell you like.

The answer is 162,889.46

Have a look at XL's help for the Future Value (FV) function.

This message is now public, and will not be pursued through any court. :-)

Regards,
Kevin
 
Thanks for this, Kevin.

Is there any way I can get it to display each year, like as it progresse?

Ed
 
"Edward" wrote in message
Thanks for this, Kevin.

Is there any way I can get it to display each year, like as it progresse?

Ed
Click to expand...
in message
Click to expand...

Edward,

B2=5%
C2=100,000
D2=(1+$B$2)*C2
Copy D2 thru to L2 (10 years or columns total)

Regards,
Kevin
 
Edward,

Here is another way that might help you.

I am starting with a clean sheet.

B1=Year 1
C1=Year 2. (copy over thru to Col K)
....
K1=Year 10

A3=Opening Balance
A4=Interest Percent
A5=Interest Amount
A6=Closing Balance

B3=100,000
B4=5%
B5=B3*B4
B6=B3+B5

C3=B6
C4=B4
C5=C3*C4
C6=C3+C5

Copy C columns through columns D through K.

Hope that helps.

Regards,
Kevin
 
Hi, Kevin.

Thanks a million for all your effort. It is very late here now so I'll be
off to bed. I'll be trying it all in the morning. I'll repost in the same
string to let you know how I get on - if you have time to track back.

Very best wishes and thanks again.

Ed
 
Hi, Kevin.

Well, you put me right on track.

I wanted the read-outs vertical (all in one column, which I did not
explain - sorry) so I played with your input.

I ended up with:

B2 5%
C2 100,000
D2 (C2+B2*C2)

I then adjusted the second year opening formula using year 1 balance and
then dragged this to the 10th year. All the figures climbed numerically by
1. I.e. C3,B3,C3; C4,B4,C4 &c. All I had to do then was open each year's
balance formula and change B3,B4 etc back to B2 and all is well.

Thanks again for your help.

Kind regards,

Ed
 
I wanted the read-outs vertical (all in one column, which I did not
explain - sorry) so I played with your input.

I ended up with:

B2 5%
C2 100,000
D2 (C2+B2*C2)

I then adjusted the second year opening formula using year 1 balance and
then dragged this to the 10th year. All the figures climbed numerically by
1. I.e. C3,B3,C3; C4,B4,C4 &c. All I had to do then was open each year's
balance formula and change B3,B4 etc back to B2 and all is well.
Click to expand...

Hi Edward,

If you are still tuning in, great.

You could have adjusted your formula in D2 as the following:

D2 =(1+B$2)*C2

And then just copy that formula down. The dollar sign in important. It
"fixes" the following column or row. In our case, it fixes the row. So the
formula says, (1+interest rate) times the prior balance. That's what you
are looking for.

By way of explanation:
$B$2...both row and column are fixed when copying
B$2...Row 2 is fixed only
$B2...Column B is fixed only
B2....neither the column or row is fixed.

I hope that helps.

Best regards,
Kevin
 
I'm glad I met you! Thanks for the explanation. I never knew. Excel is a
cracking programme but I'm not a rocket scientist, unfortunately, and find
most of the "Help" data vertually unreadable!!

Many thanks again.

Ed.
 
"Edward" wrote ...
I'm glad I met you! Thanks for the explanation. I never knew. Excel is a
cracking programme but I'm not a rocket scientist, unfortunately, and find
most of the "Help" data vertually unreadable!!

Many thanks again.

Ed.
Click to expand...

Edward,

My pleasure.

You might want to consider a couple of books by John Walkenbach.

1) Excel 2003 Bible http://tinyurl.com/2apxd
2) Excel 2003 Formulas http://tinyurl.com/259fj

Don't worry if you are using a prior version of Excel. The changes are
minimal and you should be able to follow along without difficulty. John's
an exceptional author, who has a great blog (web log) by the way
http://www.j-walkblog.com/blog/, so you can't go wrong with his Excel books.

Once you start playing with Excel, it becomes much, much easier. Though you
have to be careful, for Excel can become addictive. So be vewry, vewry
careful.

Glad I could help.

Best regards,
Kevin
 
Thanks, Kevin.



Kevin Stecyk said:
"Edward" wrote ...

Edward,

My pleasure.

You might want to consider a couple of books by John Walkenbach.

1) Excel 2003 Bible http://tinyurl.com/2apxd
2) Excel 2003 Formulas http://tinyurl.com/259fj

Don't worry if you are using a prior version of Excel. The changes are
minimal and you should be able to follow along without difficulty. John's
an exceptional author, who has a great blog (web log) by the way
http://www.j-walkblog.com/blog/, so you can't go wrong with his Excel books.

Once you start playing with Excel, it becomes much, much easier. Though you
have to be careful, for Excel can become addictive. So be vewry, vewry
careful.

Glad I could help.

Best regards,
Kevin
Click to expand...
 
Back
Top