Shortcut to multiply percentages without having to manually add (1+x%) repeatedly

[BlackieHamel]

I saw this once, but can't remember how it's done.

If I have an investment that returned the following in three years:
2.3%
4.6%
9.5%

. . . my compound return is 1.023*1.046*1.095 minus 1, or 17.17%.

If I have those three percentages in Column A, I usually create a formula in Column B to add 1 to each number in A, and then I use PRODUCT(B1:B3)-1.

But is there a function that will do this without the intermediate step of creating that Column B?

Blackie

 

[Eric W]

You can use an array formula, use Control-Shift-Enter after putting in the function:
=PRODUCT(A1:A3+1)-1

 

[BlackieHamel]

You can use an array formula, use Control-Shift-Enter after putting in the function:
=PRODUCT(A1:A3+1)-1

That's PRECISELY what I was looking for. Thanks, Eric!

(Man, I love this forum.)

Blackie

 

[Eric W]

Glad it helped. Thanks for the feedback.