How to calculate compound interest based on bottom x rows of a spreadsheet in Google Sheets

Question

Let's say I have a table with a percentage interest on each row. I would like to know how to calculate the compound interest based on the bottom x number of rows.

For example, if each row is a day, I would like to work out interest over the last 7 days, which is the bottom 7 rows in the spreadsheet.

If the percentages are say:

Date	Profit
03/09/2021	1.26%
04/09/2021	2.86%
05/09/2021	0.08%
06/09/2021	3.94%
07/09/2021	-0.22%
08/09/2021	-14.16%
09/09/2021	-0.40%
10/09/2021	1.42%

I could calculate this manually using =1-(1+B3)*(1+B4)*(1+B5)*(1+B6)*(1+B7)*(1+B8)*(1+B9), which comes out at 7.43% which is how much the account would be down by over the last week. I can't seem to get the result as a negative for some reason.

But the main issue is as I add a new row every day I would have to manually change the formula to reflect the bottom 7 rows cell numbers.

Is there any way in Google Sheets that I can get the bottom 7 rows and do this formula automatically?

For more simple equations, i.e. adding the bottom 7 values together, I've previously used something like the below:

=if((COUNTA(B2:B1000))<7,"NOT ENOUGH DATA", SUM(FILTER('B2:B1000,ARRAYFORMULA(ROW('B2:B1000)>COUNT('B2:B1000)-7))))

However this would not work for compound interest as you can't simply add the values together.

Answer 1

It seems your data shown in the post runs from A2:B (with headers in A1 and B1). That being the case, the following formula should return the result you want:

=ArrayFormula(PRODUCT(1+SORTN(B2:B,7,1,A2:A,0))-1)

SORTN will isolate 7 entries in B2:B as sorted in highest-to-lowest order (parameter 5) by A2:A. That will give be the bottom-most entries on your physical sheet.

1+ is applied to each of these 7 numbers.

The PRODUCT of all of those is returned.

-1 is applied to that product to remove the original amount. (You were incorrectly adding this amount instead of subtracting it, which is why your result was not negative when you expected it to be.)