I’m working with a sample sheet available here.

In the “loans” sheet, there’s a list of loans with their respective due dates, and each loan is associated with a specific loan date (Column A) and currency (Column E).

Additionally, there’s a “payments” sheet containing payment information, including the payment date, amount, and currency.

What I need to achieve is to calculate the pending amount to pay for each loan in the “loans” sheet, considering the currency.

To illustrate this, I have included column H in the “loans” sheet with the expected result.

It’s crucial that this calculation is done using a single array formula to avoid manual dragging. Furthermore, it should take into account the currency when calculating the pending amount for each loan.

my first approach was this formula

={"pending amount";
ARRAYFORMULA(
IF(LEN(C2:C)*(B2:B<=TODAY()),

IF(MMULT( (ROW(C2:C)>=TRANSPOSE(ROW(C2:C))) * (C2:C=TRANSPOSE(C2:C)), D2:D * (C2:C<>"") )-
IF(B2:B<=TODAY(),SUMIF(Payments!B2:B,C2:C,Payments!C2:C),)>0,

MMULT( (ROW(C2:C)>=TRANSPOSE(ROW(C2:C))) * (C2:C=TRANSPOSE(C2:C)), D2:D * (C2:C<>"") )-
IF(B2:B<=TODAY(),SUMIF(Payments!B2:B,C2:C,Payments!C2:C),)

,0)

,)
)
}

The problem is that this formula utilizes MMULT and significantly slows down the sheet.

my second approach is this:

={"pending amount";
ARRAYFORMULA(
IF(LEN(C2:C)*(B2:B<=TODAY()),

LET(
loan,  
SUMIF( MATCH(ROW(C2:C), SORT(ROW(C2:C), C2:C & E2:E, 1),), "<=" & MATCH(ROW(C2:C), SORT(ROW(C2:C), C2:C & E2:E, 1),), D2:D) - 
SUMIF( MATCH(C2:C & E2:E, SORT(C2:C & E2:E),), "<" & MATCH(C2:C & E2:E, SORT(C2:C & E2:E),), D2:D),

payment,    IF(B2:B<=TODAY(),SUMIF(Payments!B2:B&Payments!D2:D,C2:C&E2:E,Payments!C2:C),),

IF(loan-payment>0,
loan-payment,0)


)
,)
)
}

Although functional, this formula also slows down the sheet.

Any guidance or assistance on how to accomplish this efficiently would be greatly appreciated. Thank you!