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 

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