Can you calculate the Geometric Average or Compound Rate of Return

[Anonymous]

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?

[Gysbert_Wassenaar]

Can you post an example document that demonstrates the problem?

[jonathandienst · Accepted solution]

Hi

The attached model shows how to calculate compounded values, compound return and the average return per period.

What you are referring to as geometric average is actually the compound return. For a given set of returns, r1 .. rn, the geometric average is

    (1 + r1) * (1 + r2) * (1 + r3) * ..... * (1 + rn) ^ (1/n)

(ie the nth root of the compound return). This is also the average return per period.

HTH

Jonathan

[Anonymous]

An instance where the specification was drafted with a specific formula rather than an explanation of the requirement!

I had just been proposing that the compound annual growth rate be a better measure than compound growth:

∛(((End value)/(Start value)) )-1

When I realized that the "geometric average" was essentially just the period return:

((End value)/(Start value))-1

Thanks for your reply.

Nick