Forum Discussion

yossivan's avatar
yossivan
Copper Contributor
Feb 11, 2019

Help with Cash-flow schedule

Hello,   I am building a worksheet in which using contract details, I can have a calendar that displays each day's deposits. I need help with my formula that is not displaying the result requested....
excel
FORMULA ERROR
sumif
SUMIFS
yossivan's avatar
yossivan
Copper Contributor
Feb 12, 2019

Hey sergei that you for your response,

 

the contract starts at the date (under column D) but the number of installments (under column F) display the amount of monthly payments needed to be deposited. Also the end contract date (under column J) also displays the last deposit date.

 

does it clarify?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 14, 2019

Hi,

 

Perhaps it could be

=SUMPRODUCT(($D$3:$D$5=$L3)*$H$3:$H$5)+
SUMPRODUCT(
   (INT(($D$3:$D$5+($J$3:$J$5-$D$3:$D$5)/($F$3:$F$5-1)*COLUMN($A$1:$X$1)))=L3)*
   (INT(($D$3:$D$5+($J$3:$J$5-$D$3:$D$5)/($F$3:$F$5-1)*COLUMN($A$1:$X$1)))<=$J$3:$J$5)*
   $I$3:$I$5)

See in column O attached. In formula is the small range from the sample and I did'n test on all variants.