Excel function

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

  1. sallyboy

    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.

  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.


    Bernd Kuerbs
  3. sallyboy

    sallyboy Guest

    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.

  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?
  6. Nothing.
  7. Swish


    Sorry BKuerbs,

    I didn't read your post close enough!!