Can you calculate the Geometric Average or Compound Rate of Return
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 thegeometric 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":
=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.