Geometric mean for rate of return

I'm trying to calculate an annualized rate of return. I have a column of return percentages and i need a formula that will add 1 to all of the values then plug into the GEOMEAN function.

Col D
1 32.08%
2 76.92%
3 19.29%
4 9.05%

Basically I need to calculate ((1+D1)*(1+D2)*(1+D3)*(1+D4)*(1+Dn))^1/n

I appreciate any help out there.

Originally Posted by jcowan0921

Basically I need to calculate ((1+D1)*(1+D2)*(1+D3)*(1+D4)*(1+Dn))^1/n

First, the geometric mean is ((1+D1)*(1+D2)*(1+D3)*(1+D4)*...*(1+Dn))^(1/n) - 1 .

You can array-enter (press ctrl+shift+Enter instead of just Enter):

=GEOMEAN(1+D1:D4) - 1

formatted as Percentage.

Caveat: IIRC, there is some limitation in the use of GEOMEAN in this context. My recollection might be wrong. But if you encounter a limitation, you can array-enter the following instead:

=PRODUCT(1+D1:D4)^(1/COUNT(D1:D4)) - 1

That did the trick. Thank you so much. And thank you for the corrections to the formula, too. You are, of course, absolutely correct.