Excel function

Does anyone know which function in Excel will return the annualized return of a series of annual returns?

For example, over an 8 year period the individual annual returns are as follows:

20% 0% 10% -10% 30% 15% 10% 5%


Any help is appreciated.


~Sal

 

Tray the geometric mean: to be found under "functions" -> "Statistics".

You have to convert your numbers into factors: i.e. 20% to 1.20, -10% to 0.90.

The initial investment multiplied by the geo mean taken to the 8th power (= number of periods) yields the end result.

regards

Bernd Kuerbs

 

That will do it!

Thanks alot!

 

The problem w/ averaging is that is doesn't accout for the order of the returns.

For example, if you lost 50% in year one, the 20% gain in the next year should be based on the adjusted capital basis at the beginning of year 2 - thus a 20% gain in year 2 only recovers 10% or 1/5 of your loss in year 1.

Thus, I recommend that you convert the %'s to $ and calculate an rate of return based on the irr function in excel.

Swish

 

In your example an initial investment of 100 (whatever currency) will "grow" to 60 after the second period.

The factors are: 0.5 and 1.2. The geo mean is 0.77. Which translates into -23% (yearly).

(0.77)**2 * 100 yields 60. So what is wrong with that?

 

Nothing.

 

Sorry BKuerbs,

I didn't read your post close enough!!

Swish