Compound Interest - Geometric Mean

Hi All,

I am trying to work something out in MS-Excel and i cannot find the return formula to give me the desired result.

Ok here is my question ...

Lets say that i invest 100 every year for ten years. At the end of the term my final amount is 1601 approx. Returns are 126.20%-8.57%32.88%2.39%2.32%2.27%2.24%2.21%2.18%2.16%2.15%2.14%

The (arithmetic mean) for this term works out at approx 14.21% ... Of course if i compound this rate and add 100 for ten year it does not come to my final amount ... so this rate of return is not accurate!

What i want to find is the compound annual rate of return to take into account the fact that i also add 100 to my investment every year ???

The information I have are:

Starting Amount = 0
Yearly Countribution = 100
Final Amount = 1601
Individual Annual % Increases (AS ABOVE)

Can anyone tell me the fomula that will give me the Compound Rate taking into Consideration that additional amounts are being added to my investment ?

Using an Online Calculator i have establised that rate is about 8.4%, but i would like to be able to get this myself in excel using the above data.

Any help greatly appreciated

There's not a nice simple formula for what you want. Written out algebraically it's a polynomial of order 10.

1601 = 100 (x^10 + x^9 + ... + x) where x is the interest rate

Put the RHS of the formula below into say cell C6 and then play with the value in C5. (cells chosen at random).

The value in C5 is the interest rate you're looking for and the value in C6 will be 1601 when you put the right value (~ 1.084) into C5.

1601 = 100*(C5+POWER(C5,2)+POWER(C5,3)+ POWER(C5,4)+ POWER(C5,5)+ POWER(C5,6)+ POWER(C5,7)+ POWER(C5,8)+ POWER(C5,9)+ POWER(C5,10))

There may be a built in financial function in Excel that hides this complexity.

Thank you very much for your reply,

Although my suspicions have been confirmed in that i may not be able to find a simple solution to my problem ...

Although in the example given my aim in to achieve the compund rate for 10 periods, my ultimate goal is to achieve it for 360 periods which would make for a rather large Excel formula ... plus i will have to manually change it for every additional ...

I was just putting the feelers out to see if anyone has got maybe a FUNCTION to calculate this on a continuous basis as more and more rows are added to an Excel spreadsheet ...

I can manually get the rate from the numerous online savings calculators and for this ten periods it is approx 8.4% ...

But i cannot come up with a fomula that can be constructed once a pasted to the rest of the cells on the spreadsheet.

Thanks very much for your response ... It has given me much food for thought.

Sounds like you need to take it the next level up and look at a bit of Visual Basic/Goal seek.

Googling for a function might help.

There's a nice book, it has a chapter of VB and also on financial calculations.

Excel Scientific and Engineering Cookbook
By David M. Bourg
...............................................
Publisher: O'Reilly
Pub Date: January 2006
Print ISBN-10: 0-596-00879-1
Print ISBN-13: 978-0-59-600879-6
Pages: 442