Calculate AACR without needing to add 1 to each annual percentage

[BlackieHamel]

Consider $100 invested for three years with the following returns:

Initial investment $100.00 Return in Year One 6.8% 106.8% Return in Year Two -3.0% 97.0% Return in Year Three 10.6% 110.6% Terminal Value $ 114.58 4.64% <== AACR <tbody> </tbody> The values in Column C are calculated by adding 1 to the values in Column B. Here's the formula I used to calculate the Average Annual Compound Return (AACR) of 4.64%: =PRODUCT(C2:C4)^(1/3)-1 There's a more straightforward (or at least shorter) way to do this, but I have forgotten what it is. What I want is to be able to take a set of values like the above {.068, -.03,.106} and calculate the AACR without needing first to create a separate column that adds 1 to these values. Can someone enlighten me? Thanks. Blackie <strike></strike>

<tbody>
</tbody>

 

[shg]

	A​	B​	C​
1​	Year​	Return​
2​	1​	6.80%​
3​	2​	-3.00%​
4​	3​	10.60%​
5​	Average​	4.64%​	B5: {=PRODUCT(1+B2:B4) ^ (1/COUNT(B2:B4))- 1}

 

[BlackieHamel]

	A​	B​	C​
1​	Year​	Return​
2​	1​	6.80%​
3​	2​	-3.00%​
4​	3​	10.60%​
5​	Average​	4.64%​	B5: {=PRODUCT(1+B2:B4) ^ (1/COUNT(B2:B4))- 1}

<tbody>
</tbody>

Thanks, SHG. That looks like exactly the formula I seek, but I can't get it to work for me. I tried it both with the curly brackets

{=PRODUCT(1+B2:B4)^(1/COUNT(B2:B4))-1}

and without

=PRODUCT(1+B2:B4)^(1/COUNT(B2:B4))-1

.

In the first case, Excel treats this as a text string. In the second, I get a #VALUE ! error. What am I missing?

Blackie

 

[shg]

The curly braces means that the formula must be array-entered. Enter the formula sans braces, press and hold the Ctrl and Shift keys, then press Enter; the braces will appear.

An alternative, also array-entered:

	A​	B​	C​
1​	Year​	Return​
2​	1​	6.80%​
3​	2​	-3.00%​
4​	3​	10.60%​
5​	Average​	4.64%​	B5: {=GEOMEAN(1 + B2:B4) - 1}

 

[BlackieHamel]

The curly braces means that the formula must be array-entered. Enter the formula sans braces, press and hold the Ctrl and Shift keys, then press Enter; the braces will appear.

An alternative, also array-entered:

	A​	B​	C​
1​	Year​	Return​
2​	1​	6.80%​
3​	2​	-3.00%​
4​	3​	10.60%​
5​	Average​	4.64%​	B5: {=GEOMEAN(1 + B2:B4) - 1}

<tbody>
</tbody>

Aha! Thanks for that, SHG. It reminds me that I need to learn up about arrays! And thanks, too, for educating me about the GEOMEAN function. Problem solved!

Blackie

 

[shg]

Enter the formula sans braces ...

Type (paste) the formula into the formula bar, then ~

 

[shg]

You're welcome.