General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Excel function

Discussion in 'Trading Software' started by sallyboy, Feb 27, 2004.

1. ### sallyboyGuest

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

2. ### BKuerbs

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

File size:
2.8 KB
Views:
124
3. ### sallyboyGuest

That will do it!

Thanks alot!

4. ### 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

5. ### BKuerbs

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.

7. ### Swish

Sorry BKuerbs,