Forum Discussion

SYED93552's avatar
SYED93552
Copper Contributor
Aug 27, 2023

HOW TO CALCULATE SUM OF OUTSTANDING BALANCE AS OF DATE

Hi, I have to find the sum of balance contract amount, based on the criteria like contract maturity date not exceed the start date as a as of date. I'm not able to arrive through the sumif/ifs cond...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 27, 2023

SYED93552 

With 365.

Worksheet formula
= BYROW(AsOfDate, OutstandingBalanceλ(startDate,maturityDate,amount))

where OutstandingBalanceλ
= LAMBDA(start,maturity,amt,
    LAMBDA(date,
        LET(
            balance, amt *
                IF(
                    date < start,
                    1,
                    IF(maturity < adate, 0, (maturity - adate) / (maturity - start))
                ),
            SUM(balance)
        )
    )

 

Resources