UKC

Morning folks.

Can anyone help me please with a simple formula which I can adapt which allows me to calculate compound growth over a set period of time.

So, imagine I start with a value, say 10, and have a period of time, say 10 years, rather than doing the donkey work by multiplying each new value with 1.1 if I wanted to grow each year by 10% (reaching the indended 10 year compound growth figure) I would prefer to have a table into which I can enter the initial value and then simply add in the formula and it calculates the next ten values automatically.

Makes sense.

Thanks

Richard

In reply to TheDrunkenBakers:

I'll change your example to make things clearer, 20 over 10 years at 5% (otherwise you have 10, 10, 10).

Answer is:

total=20 * 1.05^10
total = initial_value * (1 + interest/100) ^ no_interest_cycles

In excell speak "=E12*(1+G12/100)^F12", where E12=20, G12=5 and F12=10

In reply to duchessofmalfi:

Perfect, thanks, Ill give that a go.

Richard

In reply to TheDrunkenBakers:

It should be ...

total=20 * 1.05^10
total = initial_value * (1 + (interest/100)) ^ no_interest_cycles

In excell speak "=E12*(1+(G12/100))^F12", where E12=20, G12=5 and F12=10

Note the additional brackets which are important!

In reply to minimike:

I think even excel understands bodmas which means the G12/100 is automatically calculated first without the bracket (ditto the ^ before the *).

In reply to duchessofmalfi:

Fair enough, I didn't test it. I don't trust excel though..

In reply to duchessofmalfi:


Random excel tip for the OP - if you put "special values" in an exact box, you can use the '$' so as to allow you to "move" your formula and have it still work. (normally when you move a formula, say a column to the right, everything moves a column to the right, but we don't want the formula using the magic numbers to move, because they're locked in place). Using a single dollar sign on either the horizontal or vertical co-ord locks only that co-ord.

This would let us put values either in the columns above/below or rows above/below E12, and move the formula cell, and have it still work.

"=E12*(1+$G$12/100)^$F$12"

In reply to TheDrunkenBakers:

Pretty sure Excel has a built in worksheet function for compound growth.

In reply to Lion Bakes:

you mean like FVSCHEDULE
LD

In reply to duchessofmalfi:
I set up the SS to do this and tried a little experiment with applying the interest not only yearly but also monthly, weekly and daily. The results are that - using the figures in your example - you get £32.58, £32.94, £32.97 & £32.97 respectively.
What frequency do most financial institutions use for their calculations I wonder.

In reply to keith-ratcliffe:

Isn't that something APR/AER is meant to account for?

In reply to TheDrunkenBakers:
It was clear from my experiments that the final total reached a limiting value and it looks suspiciously like an exponential process. I Googled it and learned that it was discovered by Bernoulli and is indeed exponential. A simple formula for the final value of an amount (A) invested for (t) years at (r) % p/a expressed as decimal (e.g. 5 % is 0.05) but based on constantly applied compound interest is given by Ae^rt where e is the euler number - 2.8718 etc. This is as pointed out the basis of AER/APR figures.