Excel function

S

sallyboy

Guest
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
 

Attachments

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
 
Quote from Swish:

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
More...

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?
 
You must log in or register to reply here.
Back
Top