Calculate AACR without needing to add 1 to each annual percentage

[BlackieHamel]

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

[TABLE="width: 326"]
<tbody>[TR]
[TD][TABLE="width: 445"]
<tbody>[TR]
[TD]Initial investment[/TD]
[TD="align: right"]$100.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Return in Year One[/TD]
[TD="align: right"]6.8%[/TD]
[TD="align: right"]106.8%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Return in Year Two[/TD]
[TD="align: right"]-3.0%[/TD]
[TD="align: right"]97.0%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Return in Year Three[/TD]
[TD="align: right"]10.6%[/TD]
[TD="align: right"]110.6%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terminal Value[/TD]
[TD] $ 114.58[/TD]
[TD="align: right"]4.64%[/TD]
[TD]<== AACR[/TD]
[/TR]
</tbody>[/TABLE]

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>[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Year​

[/td][td="bgcolor:#F3F3F3"]

Return​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

6.80%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2​

[/td][td]

-3.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3​

[/td][td]

10.60%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#F3F3F3"]

Average​

[/td][td="bgcolor:#E5E5E5"]

4.64%​

[/td][td]B5: {=PRODUCT(1+B2:B4) ^ (1/COUNT(B2:B4))- 1}[/td][/tr]
[/table]

 

[BlackieHamel]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Year​

[/TD]
[TD="bgcolor: #F3F3F3"]

Return​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

6.80%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

-3.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

10.60%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD="bgcolor: #F3F3F3"]

Average​

[/TD]
[TD="bgcolor: #E5E5E5"]

4.64%​

[/TD]
[TD]B5: {=PRODUCT(1+B2:B4) ^ (1/COUNT(B2:B4))- 1}[/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Year​

[/td][td="bgcolor:#F3F3F3"]

Return​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

6.80%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2​

[/td][td]

-3.00%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3​

[/td][td]

10.60%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#F3F3F3"]

Average​

[/td][td="bgcolor:#E5E5E5"]

4.64%​

[/td][td]B5: {=GEOMEAN(1 + B2:B4) - 1}[/td][/tr]
[/table]

 

[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:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Year​

[/TD]
[TD="bgcolor: #F3F3F3"]

Return​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

6.80%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

-3.00%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

10.60%​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD="bgcolor: #F3F3F3"]

Average​

[/TD]
[TD="bgcolor: #E5E5E5"]

4.64%​

[/TD]
[TD]B5: {=GEOMEAN(1 + B2:B4) - 1}[/TD]
[/TR]
</tbody>[/TABLE]

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.