Home

Help with Cash-flow schedule

%3CLINGO-SUB%20id%3D%22lingo-sub-334862%22%20slang%3D%22en-US%22%3EHelp%20with%20Cash-flow%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334862%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20building%20a%20worksheet%20in%20which%20using%20contract%20details%2C%20I%20can%20have%20a%20calendar%20that%20displays%20%3CSTRONG%3Eeach%20day%3C%2FSTRONG%3E's%20deposits.%20I%20need%20help%20with%20my%20formula%20that%20is%20not%20displaying%20the%20result%20requested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20best%20describe%20my%20issue%2C%20here%20is%20a%20picture%20of%20my%20worksheet%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72791iE7644C41F6AF0853%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22screenshot.PNG%22%20title%3D%22screenshot.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20columns%20from%20B%20to%20F%20are%20input%20column%20where%20I%20write%20the%20information%20of%20the%20contract.%20The%20column%20H%20to%20J%20are%20calculated%20using%20basic%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20seems%20to%20be%201%20main%20issue%20with%20my%20formula.%20For%20the%20first%20month%2C%20from%20cell%20M3%20until%20M35%2C%20the%20result%20is%20correct%20but%20from%20M36%20on%2C%20it%20doesn't%20display%20any%20of%20the%20contract%20planned%20deposits.%20any%20idea%20what%20is%20wrong%20and%20how%20to%20fix%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20excel%20file%20as%20a%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20the%20solution%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excelforum.com%2Fexcel-formulas-and-functions%2F1263565-cash-flow-planning-per-day.html%23post5061110%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelforum.com%2Fexcel-formulas-and-functions%2F1263565-cash-flow-planning-per-day.html%23post5061110%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-334862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFORMULA%20ERROR%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esumif%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESUMIFS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-340025%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Cash-flow%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-340025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%24D%243%3A%24D%245%3D%24L3)*%24H%243%3A%24H%245)%2B%0ASUMPRODUCT(%0A%20%20%20(INT((%24D%243%3A%24D%245%2B(%24J%243%3A%24J%245-%24D%243%3A%24D%245)%2F(%24F%243%3A%24F%245-1)*COLUMN(%24A%241%3A%24X%241)))%3DL3)*%0A%20%20%20(INT((%24D%243%3A%24D%245%2B(%24J%243%3A%24J%245-%24D%243%3A%24D%245)%2F(%24F%243%3A%24F%245-1)*COLUMN(%24A%241%3A%24X%241)))%26lt%3B%3D%24J%243%3A%24J%245)*%0A%20%20%20%24I%243%3A%24I%245)%3C%2FPRE%3E%0A%3CP%3ESee%20in%20column%20O%20attached.%20In%20formula%20is%20the%20small%20range%20from%20the%20sample%20and%20I%20did'n%20test%20on%20all%20variants.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338250%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Cash-flow%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338250%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20sergei%20that%20you%20for%20your%20response%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20contract%20starts%20at%20the%20date%20(under%20column%20D)%20but%20the%20number%20of%20installments%20(under%20column%20F)%20display%20the%20amount%20of%20monthly%20payments%20needed%20to%20be%20deposited.%20Also%20the%20end%20contract%20date%20(under%20column%20J)%20also%20displays%20the%20last%20deposit%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20it%20clarify%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334999%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Cash-flow%20schedule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334999%22%20slang%3D%22en-US%22%3E%3CP%3EBut%20you%20have%20no%20dates%20after%20the%20Feb%2001%2C%20after%20that%20date%20all%20shall%20be%20zero%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20149px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72817i3949E1102839979D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
yossivan
New Contributor

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.

 

To best describe my issue, here is a picture of my worksheet:

screenshot.PNG

The columns from B to F are input column where I write the information of the contract. The column H to J are calculated using basic formulas.

 

There seems to be 1 main issue with my formula. For the first month, from cell M3 until M35, the result is correct but from M36 on, it doesn't display any of the contract planned deposits. any idea what is wrong and how to fix it?

 

 

I have attached my excel file as a reference.

 

Your help is greatly appreciated.

 

here is the solution: https://www.excelforum.com/excel-formulas-and-functions/1263565-cash-flow-planning-per-day.html#post...

3 Replies

But you have no dates after the Feb 01, after that date all shall be zero

image.png

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?

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies