Google-sheets – How to calculate compound interest for an entire range of percentages

google sheetsworksheet-function

I have a range of percentages in cells A1:A5. I want to calculate how much all of these percentages compound up to and give a final factor.

e.g., 1.00 times A1 (10%) times A2 (6.5%) is 1.1715.

I can't think of a way to do this dynamically. The best I have is:

=(1 + A1) * (1 + A2) ...

which is not graceful at all.

The range length constantly changes as I enter new data so a fixed approach like that just doesn't work. The range could be A1:A3, then A1:A9999 for example.

Best Answer

In one cell, 

=arrayformula(product(1+A1:A))

does the job. Empty cells become 1 and do not affect the product. If there is a concern about unnecessary multiplication of many 1s coming from empty cells, use the filtered version

=arrayformula(product(1+filter(A1:A, len(A1:A))))
Related Topic