Calculate Invoice Ageing settlement on FIFO basis

Hello All ,

I'm looking for excel function, which can calculate invoice ageing settlement based on FIFO order. With the help of you guys, had managed to calculate Invoice due amount on FIFO basis however stucked while calculating invoice ageing. I want to cal. number of days in which any random invoice get settled/paid off.

Eg (As per attachment):
Invoice number 1144 dated 30-Apr-19 got paid off on 04-May-19, hence ageing will be 4 days.
Invoice number 1155 dated 03-May-19 got paid off on 10-May-19, hence ageing will be 7 days.
Invoice number 1166 dated 08-May-19 got paid off on 10-May-19, hence ageing will be 2 days.

Since dealer will keep on invoicing and releasing payment on day to day basis, I'm unable to keep track of number ageing days in which invoice got settled.
Though I had framed some IF functions for ageing calculation, but its not working in above scenarios hence I had highlighted these ageing days in attachment with Yellow color and had manually calculated it. Ageing only to be calculated if invoice got settled.

Any help will be much appreciated.
Thanks.

Hi XLent,

Thanks for your swift revert and resolution to my query. This exactly what I was look for. Thanks again.

Hi XLent,

I need your help on one more issue in same file. Want formula in column O, wherein a 'interest chargeable amount', from respective invoices,
need to be populated after getting adjusted from payments in FIFO order.
Logic: Every invoice amount will be chargeable after getting adjusted with payments. Invoices will be adjusted on FIFO order.If there is
residual payment after invoice adjustment/knock off, 'interest chargeable amount' of new invoice will be "Residual payment amount-Invoice amount"

Eg: Residual amount: Rs. 10
New invoice amount: Rs. 50
Interest chargeable amount: Rs. 40 (Rs.10-Rs.50)

Remarks added in Col P of attached file. Desired output (in col. O) with remarks (in Col. P) entered manually ,highlighted with colors.

Thanks

Hi XLent,

Thanks, this is a great help, formula works absolutely fine.