Calculate Cumulative Return from Periodic Returns not in Percentage Format

[KoolAid]

I have a column of numbers representing periodic returns (but expressed without the %):
1.31
0.21
-0.33

I want to calculate a cumulative return of the "% changes" above (the answer is 1.1877% in this simplified case), but with some restrictions that make this hard for me to figure out:

1. I'd rather not add more intermediate calculation columns to the data, to keep the spreadsheet as simple/clean as possible. This rules out the normal solution of converting them to a %, creating a new column where you add 1 to that %, and then using =Product(Range)+1 on the new column.
2. I'd rather not use array formulas, if possible, to make this spreadsheet more usable by non-experts
3. The column of numbers above is auto-generated by a program I can't easily change, so I'd rather not have to manually modify the column of numbers above by diving by 100, etc. since any changes I make will be overwritten each time the program runs.
4. I'd prefer not to use a complex/repetitive formula like =((1+A1)*(1+A2)*(1+A3)....)-1 since there might be 100+ returns/cells to reference.

Is there any reasonable way to do what I want? Sorry for all of the restrictions!

 

[Eric W]

Welcome to the forum.

Short answer: Nope!

You've eliminated about every option. Except VBA, and based on your desire to keep things simple, I'd guess you don't want that either. Here are a couple options:

	A	B
1	1.31	1.187725922
2	0.21	1.187725922
3	-0.33	1.872717

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B3 =FVSCHEDULE(1,A1:A3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B1 {=(PRODUCT(1+A1:A3/100)-1)*100} B2 {=(FVSCHEDULE(1,A1:A3/100)-1)*100} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

B1 is a classic array formula, and works fine.

B2 uses the built-in FVSCHEDULE function, which does pretty much what you want, but it requires the interest rates to be in the usual percentage format. In order to get that, I divided the range by 100, which required making it an array formula. Or you could use a helper column, which you also didn't want to do.

Just out of curiosity, I tried FVSCHEDULE without dividing by 100, and I got the answer in B3. It "looks" pretty close to the value in B1. Divide by 100 and add 1, and you have a non-array formula. But the logic is extremely suspect, and you can't rely on it. I'd have to do a bit of mathematical analysis to see if there's a way to make it work without arrays. If I figure it out, I'll let you know.

 

[KoolAid]

Thanks for your help, Eric. I decided to go ahead and add the extra intermediate calculation columns where I converted the numbers to a %, added 1, and used the standard =Product() formula, similar to your suggestion. It is accurate and works well enough!

I would have also been OK with a VBA function since it would make the spreadsheet formula more readable, but I imagine it would be slower on large files, so I'm OK with the current compromise. Thanks again!

 

[Eric W]

Glad you got it sorted.