3 Replies Latest reply: Oct 9, 2014 5:07 PM by Nicholas Scott RSS

    Can you calculate the Geometric Average or Compound Rate of Return

    Nicholas Scott

      Is there a method of Calculating the geometric average of a number of returns (%) or the compound rate of return?

      When calculating the % rate return over more than a single month, to obtain the true periodic rate of return (Ravg) the rate of return for each individual month (R1-n) within the period must be aggregated by calculating the geometric average of the monthly returns.

      The mathematical formula to calculate a geometric average is:

       

      Ravg = [ (1 +R1) * (1+R2) * (1+R3)…(1+Rn)] – 1

       

      For example, if the rate of return for January is 1%; for February is 1.5% and for March is 0.75%, the Rate of Return for the period from January to March is:

       

      Return =  (1.01 * 1.015 * 1.0075) – 1 = 3.28%.

       

      This is not the same as the Sum of the Average Returns which is 3.25% as the geometric average is the compound rate of return.

      I have tried using the concat() function to create an expression where the Rate of Return for the monthly periods is in a field "Rate of Return":

      Simple version

       

      =concat(1+ [Rate of Return],'*')

       

      This will generate at text string : 1.01*1.015*1.0075 which will return the "geometric average" when evaluated with macro expansion "$=()".  However, this cannot be used in a chart with dimensionality as the variable is evaluated outside the chart and the same result is returned for each attribute.

       

      Has anyone come across a solution to this?