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 condition.

Can you suggest me way to arrive, as the sample that i attached

 

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)
        )
    )

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 27, 2023

    SYED93552 

    I am not sure whether the date intervals you specify should be inclusive or exclusive of end dates, but putting that aside you could have a formula

    = SUM( 
    amount * IF(aDate<startDate, 1, 
      IF(maturityDate<aDate, 0, (maturityDate - aDate) / (maturityDate - startDate))
    )
   )

    To get an array of results you would need 365 and to introduce Lambda functions and MAP.

Resources