# Payment term and Cash flow (formula linked with costs)

##### New Member
Hi everyone, I need to calculate automatically the cash position (cashout) based on payment terms and costs.
The payment terms may change, so I need a dynamic formula.
Attached you will see that there is a section with "P&L impact", with the listing of all the costs and their value month by month (if any).

Also, you will see in the "Cash flow impact" section that I need to find a formula (in cells S19 to Q28) which depends on 2 things:
1/ Cost value (from cells F5 to Q14)
2/ Payment term in days (cells S19 to S28)

For instance, if I put the value 8000 in cell F5, and Payment term in cell S19 is 40 days, thanks to the formula we will have automatically the value 8000 in cell G19.

#### Attachments

• Excel issue.PNG
51.6 KB · Views: 27

##### Well-known Member
First Test this formula at F19:
Excel Formula:
``=IF(SUM(\$F5:F5)=0,0,IF(AND(F\$18-INDEX(\$F\$18:\$Q\$18,1,MATCH(0,\$F5:F5,-1))<\$S19,EOMONTH(F\$18,0)-INDEX(\$F\$18:\$Q\$18,1,MATCH(0,\$F5:F5,-1))>\$S19),SUM(\$F5:F5)-SUM(E19:\$F19),0))``

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

##### Well-known Member
Why you changed Dates from Day 1 of month to Day 22.
1. Change days to first day of Month.
2. AND Change S19 to S28 Number format to Custom and Write at Types
Excel Formula:
``+ ##0 " Day(s)"``
With this format you only need input number others Inserted automatically.

And Post #11 formula works correctly.

##### New Member
1/ What do you mean by changing dates ? which cells are you talking about ? Cells D2 and E2 ? Or cells F4 to Q4 ?

2/ Your format does not work. Is it ok if I put this format instead ? _(+# ##0" day(s)"
This format works.

##### New Member
Your formula does not work ....

#### Attachments

• 1609581049846.png
76.6 KB · Views: 3

##### New Member

Your formula does not work ....
Haveyou got the solution @maabadi ? Thank you so much again.

##### Well-known Member
I work on it. & find.

##### New Member

I work on it. & find.
Thank you so much! Looking forward to your outcome

##### New Member
Thank you so much! Looking forward to your outcome
Have you found something? It appears to be a difficult formula, it's a really interesting case!

##### Well-known Member
Try this:
Book11.xlsx
CDEFGHIJKLMNOPQRS
1
2Start Date1/1/2021
3
4Nature of Expense1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-Dec
5Expense No. 1800060002000400000000000
6Expense No. 2004000000000000
7Expense No. 300000500000000
800010000000000
900500
1010000
116000
122000
133000
141000
15
16
17
18Nature of Expense1-Jan1-Feb1-Mar1-Apr1-May1-Jun1-Jul1-Aug1-Sep1-Oct1-Nov1-DecPayment Terms(s)
19Expense No. 1080006000200040000000000+ 40 Day(s)
20Expense No. 2000000004000000+ 200 Day(s)
21Expense No. 300000000000500+ 190 Day(s)
2200000000010000+ 200 Day(s)
2300000000050000+ 100 Day(s)
2400000001000000+ 100 Day(s)
25000000600000000+ 100 Day(s)
26000000002000000+ 100 Day(s)
27000000000030000+ 100 Day(s)
28000000000100000+ 100 Day(s)
29SubTotal
Sheet2
Cell Formulas
RangeFormula
F19:Q28F19=IFERROR(IF(SUM(\$F5:F5)=0,0,IF(AND(INDEX(\$F\$4:\$Q\$4,,MATCH(TRUE,INDEX(\$F5:\$Q5>0,0),0))+\$S19>F\$18,INDEX(\$F\$4:\$Q\$4,,MATCH(TRUE,INDEX(\$F5:\$Q5>0,0),0))+\$S19<EOMONTH(F\$18,0)),INDEX(\$F5:\$Q5,,MATCH(TRUE,INDEX(\$F5:\$Q5>0,0),0)),IF(INDEX(\$F\$4:\$Q\$4,,MATCH(TRUE,INDEX(\$F5:\$Q5>0,0),0))+\$S19>F\$18,0,INDEX(\$F5:\$Q5,,MATCH(TRUE,INDEX(\$F5:\$Q5>0,0),0)+COLUMNS(\$F19:F19)+1-IFERROR(MATCH(TRUE,INDEX(\$E19:E19>0,0),0),1))))),"")

#### Attachments

• 222333.jpg
152.2 KB · Views: 1

##### New Member
Hi @maabadi thank you very much. Unfortunately when I insert the formula in my excel file, it does not work (mayne a problem of "coma" or something like that?).

It seems to be perfectly working with your example above ... or maybe could you just send me your file with the formula (directly in the sheet, to be sure it works)? Thank you so much again.

Replies
1
Views
47
Replies
5
Views
385
Replies
2
Views
423
Replies
1
Views
294
Replies
0
Views
420