Add 1 to each percentage in range within formula

Hello,

I am trying to solve this issue. I constantly am having to use these formulas for analysing investment returns:

=((1+r1)*(1+r2)*(1+r3)....etc...)-1
=(((1+r1)*(1+r2)*(1+r3).....etc...)^(1/#years))-1

The problem is that I can be dealing with 1000s of returns. In the past I have solved this by creating separate columns somewhere else where I add 1 to each return. Lets say I put this column in column Z, then I use these columns to compute the formulas as so:

=product($Z$1:Z#)-1
=(product($Z$1:Z#)^(1/#yrs))-1

Since I have to do this so often, I would prefer if I didn't have to create these new columns every time which seems sloppy and takes time.

Does anyone have a 1 formula solution for each of these?

Thank you.

Are you using Excel in Office/MSFT 365? Your use of Z# makes it appear likely, but your Office version of 360 doesn't actually exist.

If you have Office/MSFT 365, and your rates were in R1:R1000, try

=PRODUCT($R$1:$R$1000+1)-1

=GEOMEAN($R$1:$R$1000+1)-1

If you don't actually have Office/MSFT 365 but an earlier version, try

=PRODUCT(INDEX($R$1:$R$1000+1,0))-1

=GEOMEAN(INDEX($R$1:$R$1000+1,0))-1

or just enter the 1st set of formulas as array formulas.

CORRECTION: I flubbed the GEOMEAN formulas. If there could be any blank cells in R1:R1000, you should use array formulas, specifically,

=GEOMEAN(IF(ISNUMBER($R$1:$R$1000),$R$1:$R$1000+1))-1