How to Calculate Accumulated Investment with Interest and Expenses in Google Sheets? (with arrayformulas)

I'm working on a Google Sheets project to calculate the total accumulated value of an investment, considering both cash inflows and outflows, as well as an interest rate. I want to display the accumulated total month by month, along with the monthly earnings and taxes applied to the earnings.

Problem Description: I have the following columns in my Google Sheets:

Column A: Cash Inflows

Column B: Expenses

Column C: Earnings (calculated based on the accumulated total from the previous month)

Column D: Taxes (calculated based on earnings)

Column E: Total Accumulated

The tax is applied to the total earnings, and the earnings are calculated based on the accumulated total from the previous month.

Expected Results:

• For Month 1, the total accumulated is 800 (1000 - 200).
• For Month 2, the earnings are based on the total accumulated from Month 1. If we assume an interest rate of 1% per month, the earnings would be 800 * 1% = 8. The tax is then calculated as 8 * 20% = 1.6, and the total accumulated becomes 800 + 500 - 100 + 8 - 1.6 = 1206.4.
• For Month 3, the earnings are 1206.4 * 1% = 12.064. The tax is 12.064 * 20% = 2.413, and the total accumulated becomes 1206.4 + 300 - 50 + 12.064 - 2.413 = 1465.687.

I tried using SCAN, but it only passes one variable on (the accumulator) and couldn't solve it. I tried as well an Arrayformula with seted up with an offset (like starting the array one row below or above), but it isn't what I was lookgin for.

Question: How can I set up the formulas in Google Sheets to calculate these values without creating circular references? Columns A and B are inputs and I need the formulas for columns C, D and E. I want to implement these calculations using array formulas (like SCAN(), MAP(), or BYROW() and alikes) for better performance.

You can try:

Earnings (D2)

=ARRAY_CONSTRAIN({0;FILTER(F2:F*1%,F2:F)},COUNTA(F2:F),1)

Taxes (E2)

=FILTER(D2:D*20%,D2:D<>"")

Total Accumulated (F2)

=SCAN(,FILTER(B2:B-C2:C,A2:A),LAMBDA(a,c,c+1.008*a))

Where 1.008 is 1 + 1% - 1% * 20%, or more generally, 1 + interest_rate - interest_rate * 20%.

Note: These formulas assume `Month` is column A of the sheet.