Function for calculating investment returns

[markbrinkley53]

Is there a function, or a short cut, in Excel for calculating the
annual increase needed in an investment to get from initial value A to
closing value B in a given number of years? I can work this out by
giving each year a row in a speadsheet and fiddling with the data so
that the interest rate or the return rate matches the data, but I feel
there should be a more elegant way of achieving this.

For instance, I track local house prices. Over a twenty year period,
they have gone up four fold. That's a 300% increase on the opening
value in 1986. If I divide 300% by 20, I get a figure of 15% increase
per annum. But this is wrong. The four fold increase is actually
achieved by a more modest looking 7.2% annual growth rate. So I am
looking for a formula that would produce this 7.2% figure from the
following information: the two values (opening and closing) and the
number of years between them.

Mark

 

[Domenic]

Assumptions:

A1 contains the initial value, such as 10,000

A2 contains the closing value, such as 40,000

A3 contains the number of periods (years), such as 20

Formula:

=RATE(A3,,-A1,A2,1)

....which will return an average annual compounding rate of 7.2% (rounded
to one decimal place).

Hope this helps!