# 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: 39

##### Well-known Member
With the Last code Copy all of it .
At Excel window Press ALT+F11 to go VBA window. at above panel go to Insert then Module and Paste the code at right window that appeared.
Then close vba Window and at Excel window Save as your file as Macro-Enabled workbook (.xlsm). Now you can use this function same as excel function.
input at first cell (M28)
Excel Formula:
``=ArrangeData(M27,\$EV28)``
then you can drag it to total table or copy to other tables.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### Well-known Member
This is your file with Function. Only Need to write Formula same as Excel formula.
I copy your Test2 sheet and at Test2(2), I test function for all cells.

ArrangDataFunction

##### New Member
This is your file with Function. Only Need to write Formula same as Excel formula.
I copy your Test2 sheet and at Test2(2), I test function for all cells.

ArrangDataFunction
Hi @maabadi I will try to insert the Macro excel code right now (in the file ... with the steps you mentioned above).
Just in case, do you know if it is possible to re-arrange the formula (without VBA code ... just the initial formula) to make it functionnal (cash flow working from one year to another one)? Or is it impossible?

Again, thank you for your undonditionnal help!

##### Well-known Member
For that formula it is very long & complicated and at wide range, Make slow running of your file.

Replies
1
Views
76
Replies
5
Views
414
Replies
2
Views
463
Replies
1
Views
372
Replies
0
Views
501

1,126,897
Messages
5,621,498
Members
415,845
Latest member
marej123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back