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

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.